none
How to : Count() and group by in t-sql

    Question

  • Hi all,
    I have a table in database and I want to show some report on web page by pulling information from it by applying aggregators.
    Below is the structure of my table and the expected output.

    Table:

    Date           Month    Year     IsPresent
    ----------------------------------------------
    1/1/2009     Jan        2009     True
    1/2/2009     Jan        2009     False
    1/3/2009     Jan        2009     False
    2/1/2009     Feb        2009     True
    2/2/2009     Feb        2009     False
    2/3/2009     Feb        2009     False
    3/1/2009     Mar        2009     True
    3/2/2009     Mar        2009     False
    3/3/2009     Mar        2009     False
    4/1/2009     Apr        2009     True
    4/2/2009     Apr        2009     False
    5/1/2009     May       2009     True
    5/2/2009     May       2009     False
    6/1/2009     Jun       2009     True
    6/2/2009     Jun       2009     False
    7/1/2009     Jul        2009     True
    7/2/2009     Jul        2009     False
    7/3/2009     Jul        2009     False

    Like that for a given month thousands of records are there...
    For example, from the UI, if user select year then I need to show them how many are present in the month wise. So, need to group by month, year.

    Expected output will be:
    Month    Year   Total  Attended   Absent
    ------------------------------------------------
    Jan        2009   3       1              2
    Feb        2009   3       1              2
    Mar        2009   3       1              2
    Apr        2009   2       1              1
    May       2009   2       1              1
    Jun        2009   2       1              1
    Jul         2009   3       1              2

    So, depends on the value in IsPresent column in database, I need to count it. For a given year and every month I need above count.

    Can you please help me out how can I solve this problem?
    Any suggestions will be greatly appreciated.

    thanks


    ASP.NET and SharePoint developer
    Company: http://www.rampgroup.com/
    Blog: http://praveenbattula.blogspot.com
    Sunday, November 08, 2009 9:50 AM

Answers

  • Hi  Praveen ,

     Here is what you need to try :

    Select [Month],[Year],Count(IsPresent) as [Total], Count(Case When IsPresent='True' Then IsPresent End) as Attended, Count(Case When IsPresent='False' Then IsPresent End) as Absent From <TableName> Group By [Month],[Year]

    Replace <TableName> with the actual table name .


    Cheers
    Anand

    Sunday, November 08, 2009 10:00 AM