How find the record count from SQL output ?

matkwan · #1 (**permalink**) 07-26-02, 10:31

Hi, I want to find out how many records return from the SQL, I have tried this but didn't work, please help. Thanks Matt

Select Count(CountOfQuote_ID) From (SELECT Count(tbl_Quotes.Quote_ID) AS CountOfQuote_ID, Format(tbl_Quotes.Quote_Date, "mmm yyyy") AS MonthYear, Year(tbl_Quotes.Quote_Date) AS YearDate, Month(tbl_Quotes.Quote_Date) AS MonthDate
FROM tbl_Quotes
GROUP BY Format(tbl_Quotes.Quote_Date, "mmm yyyy"), Year(tbl_Quotes.Quote_Date), Month(tbl_Quotes.Quote_Date)
ORDER BY Year(tbl_Quotes.Quote_Date), Month(tbl_Quotes.Quote_Date)))

r937 · #2 (**permalink**) 07-26-02, 10:38

see Result set row count along with query results

rudy
http://rudy.ca/

matkwan · #3 (**permalink**) 07-26-02, 11:00

Sorry Rudy, I don't get it. Do you mind using your way to change my code and show me this solution ?

Thanks
Matt

r937 · #4 (**permalink**) 07-26-02, 12:19

Code:

   SELECT Count(tbl_Quotes.Quote_ID) 
            AS CountOfQuote_ID
        , Format(tbl_Quotes.Quote_Date, "mmm yyyy") 
            AS MonthYear
        , Year(tbl_Quotes.Quote_Date) 
            AS YearDate
        , Month(tbl_Quotes.Quote_Date) 
            AS MonthDate 
     FROM tbl_Quotes 
 GROUP BY Format(tbl_Quotes.Quote_Date, "mmm yyyy")
        , Year(tbl_Quotes.Quote_Date)
        , Month(tbl_Quotes.Quote_Date) 

UNION ALL

   SELECT Count(tbl_Quotes.Quote_ID) 
        , "---Total"
        , 9999
        , 99
     FROM tbl_Quotes 

 ORDER BY 3
        , 4

note that ORDER BY 3,4 is year, month -- this is deprecated but should work in most databases