?
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
12
Medium Priority
?
283 Views
Last Modified: 2007-12-19
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
008       Osbourne   Brad      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
Comment
Question by:Ferdz_CMS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 23

Expert Comment

by:adathelad
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

by:adathelad
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

by:routledge
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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 2

Expert Comment

by:routledge
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('008','Osbourne',' Brad','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

by:hellokns
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 69

Expert Comment

by:Scott Pletcher
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

by:adathelad
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 69

Accepted Solution

by:
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

by:Lowfatspread
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

by:hellokns
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

by:Ferdz_CMS
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

by:Ferdz_CMS
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question