2 sets of count on the same field

  • I am trying to perform a Count on the same field to spilt the data up into two sets, Tests and Sessions

    SELECT Count(Lesson_CatId a) AS October_Total_Test, Count(Lesson_CatId b) AS October_Total_Sessions

    FROM Booking

    WHERE a.Lesson_CatId="001" OR a.Lesson_CatId="003" OR a.Lesson_CatId="004" OR b.Lesson_CatId="004" AND (((Lesson_DateSchedule.Lesson_Date) Between #10/1/2008# And #10/31/2008#));

    It is not working. How can I make this work.

  • cindy_sinath (11/9/2008)


    I am trying to perform a Count on the same field to spilt the data up into two sets, Tests and Sessions

    SELECT Count(Lesson_CatId a) AS October_Total_Test, Count(Lesson_CatId b) AS October_Total_Sessions

    FROM Booking

    WHERE a.Lesson_CatId="001" OR a.Lesson_CatId="003" OR a.Lesson_CatId="004" OR b.Lesson_CatId="004" AND (((Lesson_DateSchedule.Lesson_Date) Between #10/1/2008# And #10/31/2008#));

    It is not working. How can I make this work.

    I really cannot tell from this what you are trying to accomplish. You do not have a table aliased as 'a' or 'b' - so you cannot use either of those aliases. Also, dates in SQL Server are not delimited by # - they need to be delimited by a single quote.

    Please review the following and re-post the question with appropriate DDL, sample data and expected results.

    Best Practices: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Okay, first - you need to post in the Access forum instead of a SQL Server forum. Posting in the SQL Server forum will get you T-SQL answers which may or may not (mostly not) work in Access.

    So, to your specific question - you probably need to use Iif to calculate these different totals (this would be CASE WHEN ... THEN ... ELSE ... END statement in T-SQL). Something like:

    SELECT SUM(iif(Lession_CatId = "002", 1, 0)) AS Total_Test, SUM(iif(Lesson_CatId <> "002", 1, 0)) AS Total_Session, ...

    FROM ...

    WHERE ...

    Another approach, specific to Access would be to create two queries that return your counts for each type - then join those queries in a third query to combine them.

    Again - please post Access questions to the Microsoft Access forum.

    Thanks.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ok thank you. I've transferred everthing to the Ms Access section.

    Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply