1 / 17

GROUP BY Statement

GROUP BY Statement. The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SQL GROUP BY Syntax. SELECT column_name , aggregate_function ( column_name ) FROM table_name WHERE column_name operator value GROUP BY column_name.

dextra
Download Presentation

GROUP BY Statement

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. GROUP BY Statement The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

  2. SQL GROUP BY Syntax SELECTcolumn_name, aggregate_function(column_name)FROMtable_nameWHEREcolumn_name operator valueGROUP BY column_name

  3. Sửdụngcáchàm COUNT, SUM, MIN, MAX, AVG trêntừngnhómnhỏ: mệnhđề GROUP BY • Chiacácdòngthànhcácnhómnhỏdựatrêntậpthuộctínhchianhóm. • Thựchiệncácphéptoántrênnhómnhư: Count (thựchiệnphépđếm), Sum (tínhtổng), Min(lấygiátrịnhỏnhất), Max(lấygiátrịlớnnhất), AVG (lấygiátrịtrungbình).

  4. S BANG VD Câu SQL: Select Ten, count(So luong) As SL From VD Group by Ten Ten SL A 2 2 B 5 C D 3 Chiacácdòngthànhcácnhómdựatrêntậpthuộctínhchianhóm

  5. Vídụ

  6. SQL – MỆNH ĐỀ HAVING • Lọckếtquảtheođiềukiện, saukhiđãgomnhóm • Điềukiệncủa HAVING làđiềukiệnvềcáchàmtínhtoántrênnhóm (Count, Sum, Min, Max, AVG) vàcácthuộctínhtrongdanhsách GROUP BY.

  7. SELECTcolumn_name, aggregate_function(column_name)FROM table_nameWHEREcolumn_name operator valueGROUP BYcolumn_nameHAVINGaggregate_function(column_name) operator value

  8. BANG VD Câu SQL: Select Ten, count(So luong)As SL From VD Group by Ten Having count(So luong) >2 Ten SL Ten SL A 2 C 5 2 B 3 D 5 C D 3

  9. Câutruyvấntổngquát:SELECT[DISTINCT] danh_sách_cột | hàm FROMdanhsáchcácquanhệ (hay bảng, table) [WHEREđiều_kiện] [GROUP BYdanh_sách_cột_gom_nhóm] [HAVINGđiều_kiện_trên_nhóm] [ORDER BYcột1 ASC | DESC, cột2 ASC | DESC,… ]

  10. Cáccâulệnhlồngnhau • Làcáclệnh Select trongđócóchứacáclệnhSlectkhác • Cáccâulệnhbêntrongnằmsaumệnhđề where hoặc Having củacâulệnh Select bênngoài.

  11. Xétbảng NHANVIEN trên Vídụ: ĐưaraHoTen, TenDV, Congviec, Luongcủanhữngngườicólươnglớnhớnlươngtrungbìnhcủanhânviên? -> Tìmnhânviênthỏayêucầu Select HoTen, TenDV, Congviec , Luong From NHANVIEN Where Luong> (Select AVG (Luong) From NHANVIEN)

  12. Vídụ 2 Vídụ: ĐưaraHoTen, TenDV, Congviec, Luongcủanhữngngườicólươngthấpnhấttrpngtừngđơnvị. SELECTHoten, MaDV, Luong FROM NHANVIEN WHERE (MaDV, Luong) IN (SelectMaDV, Min(Luong) From NHANVIEN Group by MaDV)

  13. Vídụ 3 ĐưaraHoTen, TenDV, Congviec, Luongcủanhữngngườicólươnglớnnhấtcủađơnvịcómãlà 0002 SELECTHoten, Luong FROM NHANVIEN WHERELuong> ALL(SelectLuong From NHANVIEN WhereMaDV ='0002')

  14. SELECT MaNV, Hoten, Luong FROM NHANVIEN WHERE Luong IN (SELECT Min(Luong) FROM NHANVIEN GROUP BY MaDV)

More Related