SQL GROUP BY

Group BY instance

Instance One

Data sheet:

Name Account Score
Zhang San Language 80
Zhang San Mathematics 98
Zhang San English 65
John Doe Language 70
John Doe Mathematics 80
John Doe English 90

Expected query Results:

Name Chinese maths English
Sheet 380 98 65
Lee 470 80 90

Create TableTestscore (Tidint Primary Key Identity(1,1), Tnamevarchar( -)NULL, Ttypevarchar(Ten)NULL, Tscorint NULL   )    Go    ---Insert DataInsert  intoTestscoreValues('Zhang San','language', the)    Insert  intoTestscoreValues('Zhang San','Mathematics',98)    Insert  intoTestscoreValues('Zhang San','English', $)    Insert  intoTestscoreValues('John Doe','language', -)    Insert  intoTestscoreValues('John Doe','Mathematics', the)    Insert  intoTestscoreValues('John Doe','English', -)    SelectTname as 'name' ,     Max( CaseTtype when 'language'  ThenTscorElse 0 End)'language',     Max( CaseTtype when 'Mathematics'  ThenTscorElse 0 End)'Mathematics',     Max( CaseTtype when 'English'  ThenTscorElse 0 End)'English'      fromTestscoreGroup  byTname

Example Two

Data sheet:

DATASTR result

2016-05-09 wins

2016-05-09 wins

2016-05-09 Negative

2016-05-10 wins

2016-05-10 Negative

2016-05-10 Negative

Expected query Results:

Date wins negative number

2016-05-09 2 1

2016-05-10 1 2

-- ------------------------------Table structure for ' score '-- ----------------------------DROP TABLE IF EXISTS' score ';CREATE TABLE' score ' (' Datastr ' )varchar(255)DEFAULT NULL, ' result 'varchar(255)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=UTF8;-- ------------------------------Records of Score-- ----------------------------INSERT  into' Score 'VALUES('2016-05-09','win');INSERT  into' Score 'VALUES('2016-05-09','win');INSERT  into' Score 'VALUES('2016-05-09','Negative');INSERT  into' Score 'VALUES('2016-05-10','win');INSERT  into' Score 'VALUES('2016-05-10','Negative');INSERT  into' Score 'VALUES('2016-05-10','Negative');SELECTU.datastr asdate,SUM(Result='win') asnumber of wins,SUM(Result='Negative') asNegative number fromScore asUWHERE 1=1 GROUP  byDatastr

Example Three

Data Sheet

Country (country)	Population (population)
China	600
United States	100
Canada	100
United Kingdom	200
France	300
Japan	250
Germany	200
Mexico	50
India	250

expected query results:

Chau	Population
Asia	1100
North america	250
Other	700

            SELECT  SUM(population), CaseCountry when 'China'      Then 'Asian'                 when 'India'      Then 'Asian'                 when 'Japan'      Then 'Asian'                 when 'United States'      Then 'North America'                 when 'Canada'   Then 'North America'                 when 'Mexico'   Then 'North America'        ELSE 'other' END fromtable_aGROUP  by  CaseCountry when 'China'      Then 'Asian'                 when 'India'      Then 'Asian'                 when 'Japan'      Then 'Asian'                 when 'United States'      Then 'North America'                 when 'Canada'   Then 'North America'                 when 'Mexico'   Then 'North America'        ELSE 'other' END; 

 

SQL GROUP BY