The SQL statement query problem

Table record A field ID, CLASS,USERNAME,NAME,AMOUNT
A table records
ID Class UserName Name Amount
1 class Zhang San mathematics 90
2 class Zhang San language 89
3 class Li Si mathematics 26
4 class Li Si language 31
5 two class Wang Wu mathematics 90
6 two class Wang Wu language 80
7 two class Zhong Wei mathematics 70


Write a statement Statistics (MSSQL), a statistical test, two of the total number of classes, Chinese examination number, total score

The final result

The total number of Chinese class score test reference number
A class of 23622
Two class 24021

Started by Gale at February 01, 2016 - 5:25 PM

Supplementary:
create table grade(
id int primary key,
class varchar(20),
UserName varchar(20),
Name varchar(20),
Score int

)
insert into grade values('1','1','A three ',' Mathematics','90')
insert into grade values('2','1','A three ',' Chinese','89')
insert into grade values('3','1','Lee four ',' Mathematics','26')
insert into grade values('4','1','Lee four ',' Chinese','31')
insert into grade values('5','2','Wang five ',' Mathematics','90')
insert into grade values('6','2','Wang five ',' Chinese','80')
insert into grade values('7','2','Zhongwei ',' Mathematics','70')
select * from grade

Posted by Marvin at February 16, 2016 - 5:40 PM

select t1.class,sum(t1.amount),t2.usernum,count(t1.username)
from A as t1
join (select class,count( distinct username) as usernum from A group by class ) as t2 
on t1.class = t2.class
where t1.name = 'Chinese'
group by t1.class,t2.usernum

Posted by Sarah at November 19, 2016 - 11:39 AM

Out of the total score...


create table Stu_del
(
id int, class varchar(50),
username varchar(50),
name varchar(50)
,amount int
)

insert into Stu_del values(1,'A class', 'three', 'Mathematics',90)
insert into Stu_del values(2,'A class', 'three', 'Chinese',89)
insert into Stu_del values(3,'A class of 'four,' Li ',' Mathematics',26)
insert into Stu_del values(4,'A class of 'four,' Li ',' Chinese',31)
insert into Stu_del values(5,'Class two ',' five 'the king,' Chinese',80)
insert into Stu_del values(6,'Class two ',' five 'the king,' Mathematics',90)
insert into Stu_del values(7,'Class two ',' Zhong Wei ',' Mathematics',70)

Posted by Gale at November 21, 2016 - 12:21 PM

create table Stu_del
 ( 
     id int,     class varchar(50),
      username varchar(50),
      name varchar(50)
      ,amount int
      )

insert into Stu_del values(1,'A class', 'three', 'Mathematics',90)
insert into Stu_del values(2,'A class', 'three', 'Chinese',89)
insert into Stu_del values(3,'A class of 'four,' Li ',' Mathematics',26)
insert into Stu_del values(4,'A class of 'four,' Li ',' Chinese',31)
insert into Stu_del values(5,'Class two ',' five 'the king,' Chinese',80)
insert into Stu_del values(6,'Class two ',' five 'the king,' Mathematics',90)
insert into Stu_del values(7,'Class two ',' Zhong Wei ',' Mathematics',70)


The select class class as, sum (amount) the total scores of as, count (distinct username) the total number of as test,
	sum(case when name='Language 'then 1 else 0 end) as language reference number
from Stu_del
group by class
drop table Stu_del

/*
The total number of Chinese class score test reference number
------------------------------------------
Two class 24021
A class of 23622
*/

Posted by Deborah at December 04, 2016 - 12:56 PM

create table grade(
id int primary key,
class varchar(20),
UserName	varchar(20),
Name	varchar(20),
Score int

)
insert into grade values('1','Class 1 ',' three ',' Mathematics','90')
insert into grade values('2','Class 1 ',' three ',' Chinese','89')
insert into grade values('3','Class 1 ',' four 'Li' mathematics,','26')
insert into grade values('4','Class 1 ',' four ',' Li, Chinese','31')
insert into grade values('5','Class 2 ',' five 'the king,' Mathematics','90')
insert into grade values('6','Class 2 ',' five 'the king,' Chinese','80')
insert into grade values('7','Class 2 ',' Zhongwei ',' Mathematics','70')

select a.class,The total score, total number of examinations, the language reference number from (
select class,sum(Score)The total score, count (1) the total number of from grade test
group by class
)a
left join 
(
select class,count(*)Chinese reference number from grade where Name='language'
group by class
)b on a.class=b.class
---------------------------------------------
The total score of class test the total number of Chinese reference number
-------------------- ----------- ----------- -----------
1 class 23642
2 class 24031

(2 row(s) affected)

Posted by Thomas at December 15, 2016 - 1:37 PM

select class,count( distinct username) The total number of as test into #t2 from stu_del group by class
select class,SUM(amount)Into as total score #t1 from Stu_del group by class
select class,COUNT(distinct username) As language reference number into #t3 from stu_del where name='language' group by class
select #t1.class,#The t1. total score, the total number of #t2. test, #t3. language reference number from #t1
left join #t2 on #t2.class=#t1.class
left join #t3 on #t3.class=#t1.class
Do want to ask is there any other way?

Posted by Gale at December 19, 2016 - 2:33 PM

create table grade(
id int primary key,
class varchar(20),
UserName	varchar(20),
Name	varchar(20),
Score int

)
insert into grade values('1','Class 1 ',' three ',' Mathematics','90')
insert into grade values('2','Class 1 ',' three ',' Chinese','89')
insert into grade values('3','Class 1 ',' four 'Li' mathematics,','26')
insert into grade values('4','Class 1 ',' four ',' Li, Chinese','31')
insert into grade values('5','Class 2 ',' five 'the king,' Mathematics','90')
insert into grade values('6','Class 2 ',' five 'the king,' Chinese','80')
insert into grade values('7','Class 2 ',' Zhongwei ',' Mathematics','70')

select a.class,The total score, total number of examinations, the language reference number from (
select class,sum(Score)The total score, count (distinct UserName) the total number of from grade test
group by class
)a
left join 
(
select class,count(*)Chinese reference number from grade where Name='language'
group by class
)b on a.class=b.class
----------------------------------------------------------------------
The total score of class test the total number of Chinese reference number
-------------------- ----------- ----------- -----------
1 class 23622
2 class 24021

(2 row(s) affected)

Posted by Thomas at December 20, 2016 - 2:51 PM

The day before yesterday not to do it?

Posted by dream at January 03, 2017 - 3:47 PM

Posted by Zero at January 07, 2017 - 4:39 PM

Don't write so complex, or your statement, after merging with simplified:
Just put you write code, written in a SQL, are simplified
select class,
       SUM(amount)As total score,
       
       count(distinct username) The total number of as test,
       
       count(case when name = 'Language 'then username else null end) as language reference number 
from Stu_del
group by class
/*
Class total score test the total number of Chinese reference number
Two class 24021
A class of 23622
*/

Posted by Zero at January 15, 2017 - 4:41 PM