+ Reply to Thread
Results 1 to 3 of 3

Multiple Counts from the Same Table

  1. #1
    Registered User
    Join Date
    12-20-2010
    Location
    england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Multiple Counts from the Same Table

    I am looking to produce multiple counts for a table in one query.

    Currently I can get the count to work for one condition

    SELECT Count(Risks.[Appeal Status]) AS [0 - 5 Days]

    FROM Risks INNER JOIN Customers ON Risks.cust_id = Customers.Cust_id

    WHERE (((Risks.[Opened Date]) Between Date() And Date()-5) AND ((Risks.[5 Day])=No

    Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));


    What I would like to do is add another count for

    WHERE (((Risks.[Opened Date]) Between Date()-6 And Date()-10) AND ((Risks.[5 Day])=No Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

    and return that into a column AS [5 - 10 Days] ( I have about 6 to run in total but understanding how to do two should point me in the right direction)

    When I try to add this it assumes it as an AND operator and returns 0 for the two columns so obviously my second query is upsetting it.

    I take it I need to nest the second query but I cant get the correct syntax...

    again, any help would be greatly appreciated

  2. #2
    Registered User
    Join Date
    12-20-2010
    Location
    england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Multiple Counts from the Same Table

    Quote Originally Posted by gatorator View Post
    I am looking to produce multiple counts for a table in one query.

    Currently I can get the count to work for one condition

    SELECT Count(Risks.[Appeal Status]) AS [0 - 5 Days]

    FROM Risks INNER JOIN Customers ON Risks.cust_id = Customers.Cust_id

    WHERE (((Risks.[Opened Date]) Between Date() And Date()-5) AND ((Risks.[5 Day])=No

    Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));


    What I would like to do is add another count for

    WHERE (((Risks.[Opened Date]) Between Date()-6 And Date()-10) AND ((Risks.[5 Day])=No Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

    and return that into a column AS [5 - 10 Days] ( I have about 6 to run in total but understanding how to do two should point me in the right direction)

    When I try to add this it assumes it as an AND operator and returns 0 for the two columns so obviously my second query is upsetting it.

    I take it I need to nest the second query but I cant get the correct syntax...

    again, any help would be greatly appreciated
    Solved it myself, in case anyone is interested

    SELECT Count(Risks.[Appeal Status]) AS [0 - 5 Days],

    (Select Count(Risks.[Appeal Status]) AS [5 - 10 Days] FROM Risks INNER JOIN

    Customers ON Risks.cust_id = Customers.Cust_id

    WHERE (((Risks.[Opened Date]) Between Date()-5 And Date()-10) AND ((Risks.[5 Day])

    =No Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"))) AS [5 - 10 Days]

    FROM Risks INNER JOIN Customers ON Risks.cust_id=Customers.Cust_id

    WHERE (((Risks.[Opened Date]) Between Date() And Date()-5) AND ((Risks.[5 Day])=No

    Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

  3. #3
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Multiple Counts from the Same Table

    Thanks for taking the time to post the solution .
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1