none
SQL Statement with multiple 'Counts'

    Question

  • Hi,

    I am trying to write an SQL statement to count the number of Good and Bad 'points' awarded to each form group in our College. I have managed to create an SQL statement that will return the good points and another for the bad points but need to write a single SQL statement to return both and am having difficulty.


    Here is the code I have written:

    SELECT Blogtable.Form, Count(Blogtable.goodbad) AS BadCount

    FROM Blogtable

    WHERE (Blogtable.goodbad ='Bad')

    GROUP BY Blogtable.Form, Blogtable.goodbad;

    This returns:

    Form Bad Points
    7C 17
    7D 8
    7E 14
    7F 23

    The code to return the Good points is near enough identical, apart from the count variable is called GoodCount and the WHERE statement looks for 'Good'.

    Please can somebody suggest how I can merge the two SQL statements into one to produce the following:

    Form Good Points Bad Points
    7C 101 17
    7D 99 8
    7E 79 14
    7F 152 23

    I need to Group by Form Name.

    Many thanks,

    Matt Courtman

    Cromwell Community College


    Matt Courtman, Network Manager, Cromwell Community College, UK




    • Edited by mcourtman Thursday, March 24, 2011 1:28 PM
    Thursday, March 24, 2011 1:21 PM

Answers

  • Naomi,

     

    Found the problem

     

     

    from BlogTable B
    inner join Descript D on B.DescriptID = D.FieldID
    GROUP BY B.Form
    
    should read
    from BlogTable B
    inner join Descript D on B.DescriptID = D.ID
    GROUP BY B.Form
    

    THIS WORKS A TREAT and I am very very grateful for your help and everyone else's (Tom's solution was indeed an answer to my original question)!

    :)

     


    Matt Courtman, Network Manager, Cromwell Community College, UK
    • Marked as answer by Ai-hua Qiu Thursday, March 31, 2011 2:02 PM
    Thursday, March 24, 2011 2:50 PM

All replies

  • SELECT Blogtable.Form, SUM(CASE WHEN Blogtable.goodbad = 'Bad' THEN 1 ELSE 0 END) AS BadCount,
     SUM(CASE WHEN Blogtable.goodbad = 'Good' THEN 1 ELSE 0 END) AS GoodCount
    FROM Blogtable
    WHERE Blogtable.goodbad IN ('Good', 'Bad')
    GROUP BY Blogtable.Form;
    

    You can remove the WHERE clause if all of the rows have either Good or Bad in the goodbad column.  It is only there for possible efficiency improvements.  Removing it will not change the result.

    Tom


    • Edited by Tom Cooper Thursday, March 24, 2011 1:28 PM Group By clause incorrect
    • Proposed as answer by Naomi NModerator Thursday, March 24, 2011 1:46 PM
    Thursday, March 24, 2011 1:27 PM
  • WITH BadPoints(Form, Points)
    AS
    (
     SELECT Blogtable.Form, Count(Blogtable.goodbad) AS BadCount
     FROM Blogtable
     WHERE (Blogtable.goodbad ='Bad')
     GROUP BY Blogtable.Form
    )
    SELECT bt.Form, 'Bad Points' = Bp.Points, 'Good Points' = Count(bt.goodbad)
    FROM Blogtable bt INNER JOIN BadPoints bp ON bt.Form = bp.Form
    WHERE (Blogtable.goodbad ='Good')
    GROUP BY Blogtable.Form

    Please mark as answer if this helps. Thank you.

    http://thebipalace.wordpress.com



    Thursday, March 24, 2011 1:35 PM
  • Tom,

    Your code works perfectly, however I have a third item I'd like to add called "Points Total" (the good/bad are the number of instances, whereas a 'good' might be worth 1 point, 2 points, 3 points etc...): 

    Here is the code to calculate the points:

    SELECT Blogtable.Form, Sum(Descript.value) AS Points

    FROM (Descript INNER JOIN Blogtable ON Descript.ID=Blogtable.descriptid) INNER JOIN StudTable ON Blogtable.studid=StudTable.ID

    GROUP BY Blogtable.Form

    ORDER BY Blogtable.Form;


    Is there a way to incorporate this into your code to produce the following:

    Form No of Goods No of Bads Points Total
    7C 101 17 600
    7D 99 8 640
    7E 79 14 543
    7F 152 23 601

    Many thanks!

    Matt


    Matt Courtman, Network Manager, Cromwell Community College, UK
    Thursday, March 24, 2011 1:52 PM
  • Try

    SELECT Blogtable.Form, SUM(CASE WHEN Blogtable.goodbad = 'Bad' THEN 1 ELSE 0 END) AS BadCount,
    SUM(CASE WHEN Blogtable.goodbad = 'Good' THEN 1 ELSE 0 END) AS GoodCount,
    count(*) as TotalCount
    FROM Blogtable
    WHERE Blogtable.goodbad IN ('Good', 'Bad')
    GROUP BY Blogtable.Form

    Thursday, March 24, 2011 1:58 PM
  • Sure, use original Tom's suggestion, just enhance it:

    SELECT Blogtable.Form, SUM(CASE WHEN Blogtable.goodbad = 'Bad' THEN 1 ELSE 0 END) AS BadCount,
     SUM(CASE WHEN Blogtable.goodbad = 'Good' THEN 1 ELSE 0 END)
     AS GoodCount,
    COUNT(BlogTable.GoodBad) as [Points Total]
    FROM Blogtable
    WHERE Blogtable.goodbad IN ('Good', 'Bad')
    GROUP BY Blogtable.Form;
    
    


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 24, 2011 1:58 PM
  • Hi Naomi,

    Thanks for your answer but your code simply adds the number of good and bad instances - this is not what I require.

    A student may be award a good mark but this maybe worth 3 points - so that's one good instance with a total of three points. You have to inner join another table to see how many points the award is worth.

    Please could you have another look for me?

    Thanks

    Matt


    Matt Courtman, Network Manager, Cromwell Community College, UK

    Thursday, March 24, 2011 2:04 PM
  • I see. Could you please post structures of all tables involved and preferably some insert statements to demonstrate what you have in these tables? If there is a 1 to 1 relation between Mark and Points Assigned, then you most likely need

    SUM(A.Assigned) as [Total Points] if you have a table with this entries:

    PointsType Assigned

    Good           10

    Bad             -1

    and join with this table based on GoodBad field

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Thursday, March 24, 2011 2:10 PM
  • Could I use code like this:

     

     

    SELECT

    Count_1=(SELECT  Count(Blogtable.goodbad) FROM Blogtable WHERE Blogtable.goodbad = 'Good'),

    Count_2=(SELECT  Count(Blogtable.goodbad) FROM Blogtable WHERE Blogtable.goodbad = 'Bad')


    Problem is this doesn't give a break down by form group, just displays two figures and if I add GROUP BY Blogtable.Form I get an error message saying The multi-part identifier "Blogtable.Form" could not be bound.

     

    If I could get this code working I think I could solve my other problem

     


    Matt Courtman, Network Manager, Cromwell Community College, UK
    Thursday, March 24, 2011 2:14 PM
  • Can you please provide the information I requested, so I can help you with the query. To answer your direct question, you need to "link" it to the table you're selecting from by using 

    SELECT
    
    Count_1=(SELECT Count(Blogtable.goodbad) FROM Blogtable WHERE Blogtable.goodbad = 'Good' 
    and Form = MainTable.Form),
    
    Count_2=(SELECT Count(Blogtable.goodbad) FROM Blogtable WHERE Blogtable.goodbad = 'Bad' 
    and Form = MainTable.Form)
    
    from MainTable
    
    

    This is the way to link your main query with subqueries. However, this is not a good performing approach and Tom's solution is usually better performance wise.


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Thursday, March 24, 2011 2:26 PM
  • There are two tables required for what I need:

    TABLE 1 NAME: Blogtable

    PURPOSE of table: Every time a student is awarded a mark a new record is created containg the following main fields:

    Field 'ID' - primary key, integer value, autonumber eg 1

    Field 'Surname' - contains text value of students surname  eg Smith

    Field 'Forename' - contains text value of students first name eg John

    Field 'Form' - contains text value of students form group eg 7C

    Field 'GoodBad' - will either contain text value 'Good' or 'Bad' eg Good 

    Field 'Descriptid' - this is a integer value which references a record in the Descipt Table eg 15

     

     

    TABLE 2 NAME: Descript

    PURPOSE of table: This contains a list of the descriptions that teachers can select.

    Field 'ID' - primary key, integer value, autonumber, used to inner join the Blogtable as mention above eg 15

    Field 'Action' - text description eg Student has represented the school....blah blah blah...

    Field 'Value' - integer value containing how many points this description is worth (can be negative or positive) eg 3

     

    Let's pretend there's no data in the BLOGTABLE - it's a new school year.

    So if student John Smith (in Form 7C) represents the school twice at an event two records will be created in the BLOGTABLE:

    1, Smith, John, 7C, Good, 15

    2, Smith, John, 7C, Good, 15

    ...

     

    The '15' will be inner joined to the Descript table and see that DescptionID 15 is worth 3 points.


    What I want to do is create a 'Form League Table' web page to display the following:

    Form Name, No of Good, No of Bads, Total Points
    In my example John in 7C has been award 2 goods, 0 bads, which is a total of 6 points (because representing the school is worth 3 points a time): 
    My web page should display the following
    Form, No of Goods, No of Bads, Total Points
    7C, 2, 0, 6 
     ...

    Does that help?
    Many thanks, Matt

     


    Matt Courtman, Network Manager, Cromwell Community College, UK

    Thursday, March 24, 2011 2:32 PM
  • Yes, thanks for providing the description.

    Here is the query you're looking for:

    select B.Form, 
    sum(case when B.GoodBad = 'Good' then 1 else 0 end) as [Good Marks],
    
    sum(case when B.GoodBad = 'Bad' then 1 else 0 end) as [Bad Marks],
    sum(D.Value) as [Total Points]
    
    from BlogTable B
    inner join Descript D on B.DescriptID = D.ID
    GROUP BY B.Form
    
    
    The above will show summary for each form. If you want it down to each student, just add student's fields and group by them as well.

     


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Thursday, March 24, 2011 2:44 PM
  • Sorry, when I paste this into my datasource I get errors
    (Am using Microsoft Visual Studio 2010)

    Matt 


    Matt Courtman, Network Manager, Cromwell Community College, UK
    Thursday, March 24, 2011 2:46 PM
  • Naomi,

     

    Found the problem

     

     

    from BlogTable B
    inner join Descript D on B.DescriptID = D.FieldID
    GROUP BY B.Form
    
    should read
    from BlogTable B
    inner join Descript D on B.DescriptID = D.ID
    GROUP BY B.Form
    

    THIS WORKS A TREAT and I am very very grateful for your help and everyone else's (Tom's solution was indeed an answer to my original question)!

    :)

     


    Matt Courtman, Network Manager, Cromwell Community College, UK
    • Marked as answer by Ai-hua Qiu Thursday, March 31, 2011 2:02 PM
    Thursday, March 24, 2011 2:50 PM