Solved

# How To Count Multiple Totals Of Multiple Columns In A Single SQL Statement Or Query And Put It On A Single DataGrid?

Posted on 2003-03-03
Medium Priority
284 Views
Hey guys, I need your help.
This is the situation...

I  Have a [Teachers] and a [Students] Tables.

[Teachers]
TCode     TSName     TName
01        Johannsen  Anne
02        McMillan   Johnny
03        Smith      Jonathan

[Students]
SCode     SSName     SName     TCode     Passed
001       Andersen   Alfred    01        0
002       Cabarle    Andrew    03        1
003       Jordan     Richard   03        1
004       King       Waldorf   02        1
005       Llamas     Eric      03        1
006       Marion     Babes     02        1
007       Narita     Pat       01        0
009       Rose       Jason     01        1
010       Shawn      Larry     03        1
011       Troy       Marvin    02        0
012       VonDike    Russell   01        1
013       Wallace    Ben       02        0
014       Washington Bill      02        1
015       Williams   Andrew    03        1

I want to display this report in 1 datagrid from 1 statement...

TSName     TName     (PStud)     (FStud)     (TStud)
Johannsen  Anne         2           3           5
McMillan   Johnny       3           2           5
Smith      Jonathan     5           0           5

*(PStud = Passed Students)
*(FStud = Failed Students)
*(TStud = Totals Students)

And is it possible to sum the totals of PStud, FStud and TStud and put in in the same select statement?

I really hope you can help me guys.

Thanx
FerdzĀ®
0
Question by:Ferdz_CMS
• 3
• 2
• 2
• +3

LVL 23

Expert Comment

ID: 8056528
Hi, this should do the trick :)

SELECT T.TSName, T.TName,
(SELECT COUNT(1) FROM Students s WHERE s.TCode = T.TCode AND Passed = 1) "PStud",
(SELECT COUNT(1) FROM Students s WHERE s.TCode = T.TCode AND Passed = 0) "FStud",
(SELECT COUNT(1) FROM Students s WHERE s.TCode = T.TCode) "TStud"
FROM Teachers
0

LVL 23

Expert Comment

ID: 8056534
Forgot the "T" alias in the from clause:

SELECT T.TSName, T.TName,
(SELECT COUNT(1) FROM Students s WHERE s.TCode = T.TCode AND Passed = 1) "PStud",
(SELECT COUNT(1) FROM Students s WHERE s.TCode = T.TCode AND Passed = 0) "FStud",
(SELECT COUNT(1) FROM Students s WHERE s.TCode = T.TCode) "TStud"
FROM Teachers T
0

LVL 2

Expert Comment

ID: 8056574
Hi, You need a sub-query for this. The following example will work:

select TSNAME, TName, (Select count(*) from Students where Students.TCode= Alias_sub.TCode and Passed = 1) as PStud,
(Select count(*) from Students where Students.TCode= Alias_sub.TCode and Passed = 0) as FStud,
(Select count(*) from Students where Students.TCode= Alias_sub.TCode ) as TStud
from Teachers Alias_sub

If you want to play withthis, paste the following into Query Analyser and run it. It will set up temporary tables (with the "#" prefix, populate them with your sample date and run the quesry to give you your results.

Good luck.
0

LVL 2

Expert Comment

ID: 8056577
Forgot my Query Analyser example, but I see you already have some other exmaples.

Drop table #Teachers
Drop Table #Students
Create table #Teachers(
TCode varchar(10),
TSName Varchar(20),
TName Varchar(20)
)

INSERT #Teachers values('01','Johannsen','Anne')
INSERT #Teachers values('02','McMillan','Johnny')
INSERT #Teachers values('03','Smith','Jonathan')

Create table #Students(
SCode varchar(10),
SSName varchar (20),
SName varchar(20),
TCode varchar(10),
Passed int)

INSERT #Students Values('001','Andersen',' Alfred','01','0')
INSERT #Students Values('002','Cabarle','Andrew','03','1')
INSERT #Students Values('003','Jordan',' Richard','03','1')
INSERT #Students Values('004','King',' Waldorf',' 02','1')
INSERT #Students Values('005','Llamas','Eric','03','1')
INSERT #Students Values('006','Marion','Babes','02','1')
INSERT #Students Values('007','Narita','Pat','01','0')
INSERT #Students Values('009','Rose',' Jason',' 01','1')
INSERT #Students Values('010','Shawn','Larry',' 03','1')
INSERT #Students Values('011','Troy',' Marvin','02','0')
INSERT #Students Values('012','VonDike','Russell',' 01','1')
INSERT #Students Values('013','Wallace','Ben',' 02','0')
INSERT #Students Values('014','Washington','Bill','02','1')
INSERT #Students Values('015','Williams',' Andrew','03','1')

select TSNAME, TName, (Select count(*) from #Students where #Students.TCode= Alias_sub.TCode and Passed = 1) as PStud,
(Select count(*) from #Students where #Students.TCode= Alias_sub.TCode and Passed = 0) as FStud,
(Select count(*) from #Students where #Students.TCode= Alias_sub.TCode ) as TStud
from #Teachers Alias_sub
0

LVL 2

Expert Comment

ID: 8057025
Try this,

SELECT tsname,tname,
SUM(CASE WHEN #students.passed = 1 THEN 1 ELSE 0 END) AS Pstud,
SUM(CASE WHEN #students.passed = 0 THEN 1 ELSE 0 END) AS Fstud,
COUNT(#students.passed) Tstud
FROM #Students,
#Teachers
WHERE #Students.tcode = #Teachers.tcode
GROUP BY tsname,tname
0

LVL 70

Expert Comment

ID: 8057533
Very similiar, perhaps slightly clearer in Total Students count:

SELECT t.TSName, t.TName,
SUM(CASE WHEN s.passed = 1 THEN 1 ELSE 0 END) AS PStud,
SUM(CASE WHEN s.passed = 0 THEN 1 ELSE 0 END) AS FStud,
COUNT(*) AS TStud
FROM Students s
INNER JOIN Teachers t ON s.TCode = t.TCode
GROUP BY t.TSName, t.TName
0

LVL 23

Expert Comment

ID: 8057591
The only problem I see, is when you have the GROUP BY clause (TSName, TName), you can't cater for teachers that have the same name....
0

LVL 70

Accepted Solution

Scott Pletcher earned 200 total points
ID: 8057861
That's an excellent point.  Query should be more like this:

SELECT t.TSName, t.TName, totals.PStud, totals.FStud, totals.TStud
FROM Teachers t
INNER JOIN (
SELECT TCode,
SUM(CASE WHEN passed = 1 THEN 1 ELSE 0 END) AS PStud,
SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END) AS FStud,
COUNT(*) AS TStud
FROM Students s
GROUP BY TCode
) AS totals ON t.TCode = totals.TCode

My problem with other method of several sub-selects is that it will require multiple scans of the student table.
0

LVL 50

Expert Comment

ID: 8059874
Sorry , I've got to dash , but i think this is basically
what you where asking for including the final totals
and considering the cases of mismatched Students and teachers......

Select Case When gc = 1 Then 'Total' else Tsname End as TsName
,Pstud,fstud,Tstud from (
Select Tcode,tsname,sum(pstud)as pstud,sum(fstud) as fstud,sum(tstud) as tstud
, grouping(tcode) as gc,grouping(tsname) as gn
from (
SELECT Coalesce(t.tcode,totals.tcode) as Tcode,
ltrim(rtrim(case when TSName is null then 'Student No teacher#' else tsname end)) + ',' +
ltrim(rtrim(case when tsname is null then '(' + totals.tcode + ')' else tname end)) as TsName,
totals.PStud, totals.FStud, totals.tstud
FROM #Teachers t
Full outer JOIN (
SELECT TCode,
SUM(CASE WHEN passed = 1 THEN 1 ELSE 0 END) AS PStud,
SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END) AS FStud,
count(*) as Tstud
FROM #Students s
GROUP BY TCode
) AS totals ON t.TCode = totals.TCode
) as X

group by tcode,tsname with rollup
having grouping(tsname) = 0
or (grouping(tcode) = 1 and grouping(tsname) = 1)

) as Y

if you run it with the temporary table examples above you get

TsName                                    Pstud       fstud       Tstud
----------------------------------------- ----------- ----------- -----------
Student No teacher#,( 01)                 2           0           2
Student No teacher#,( 02)                 1           1           2
Student No teacher#,( 03)                 1           0           1
Johannsen,Anne                            0           3           3
McMillan,Johnny                           2           1           3
Smith,Jonathan                            4           0           4
Total                                     10          5           15

hth
0

LVL 2

Expert Comment

ID: 8062109
Even if teachers have same name, including tcode in the group clause will solve that problem...

SELECT tsname,tname,
SUM(CASE WHEN #students.passed = 1 THEN 1 ELSE 0 END) AS Pstud,
SUM(CASE WHEN #students.passed = 0 THEN 1 ELSE 0 END) AS Fstud,
COUNT(#students.passed) Tstud
FROM #Students,
#Teachers
WHERE #Students.tcode = #Teachers.tcode
GROUP BY #Teachers.tcode,tsname,tname
0

Author Comment

ID: 8101121
Whoa! I just got in from my vacation and when i look at my posted question, I can't believe that there would be many response on my post here, all codes are marvelous! i learned a lot from them! I wish I could grant this 50 points to all of you, but there is one code that I tried and used on my report. I chose ScottPletcher's reply. It's simple, easy to understand and effective. :D

Thanx to all you guys who help not only me but also others that posted questions here at Experts-Exchange.com

This site is awesome!
0

Author Comment

ID: 8101145
Whoa! I just got in from my vacation and when i look at my posted question, I can't believe that there would be many response on my post here, all codes are marvelous! i learned a lot from them! I wish I could grant this 50 points to all of you, but there is one code that I tried and used on my report. I chose ScottPletcher's reply. It's simple, easy to understand and effective. :D

Thanx to all you guys who help not only me but also others that posted questions here at Experts-Exchange.com

This site is awesome!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down ā¦
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recovā¦
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll