How find the record count from SQL output ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How find the record count from SQL output ?

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2002-07-26 : 10:28:40

Hi, I want to find out how many records return from the SQL, I have tries 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)))

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-26 : 10:35:40

Matt,

What error message are you getting or is it just not returning data?

Also, please be sure that when you post in the future, you should do so in the MS Access Forum, unless you switch databases to MS SQL Server. Most of the forums here are dedicated to MS SQL Server and sometimes the SQL syntax is different.

Jeremy

matkwan
Starting Member

36 Posts

Posted - 2002-07-26 : 10:46:58

Sorry, I will make sure to post this type of question in Access forum in the future :)

The error I get was "Syntax Error in From Clause"

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-26 : 10:51:03

Sorry I didn't see this in your first post. When you select records from a subquery, you cannot use the ORDER BY in the sub query. Therefore, you should be able to use the following:

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)
)

Jeremy

matkwan
Starting Member

36 Posts

Posted - 2002-07-26 : 11:08:47

Jeremy, I am still getting the same error message

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)
)

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-26 : 11:18:12

Not sure if this will work, but I tried a subquery in Access and it changed the parentheses around the subquery, (subquery), to brackets, [subquery]. Try the following and let me know.

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)
]

Jeremy

matkwan
Starting Member

36 Posts

Posted - 2002-07-26 : 11:23:16

No it doesn't work, It complains the SQL in the [] is too long and can't find it (may be it thinks is a table).

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-26 : 11:30:19

I'm not sure on this one. Again dealing with Access is much more troublesome than MS SQL. Try aliasing the subquery. Honestly, I am guessing on some of the suggestions now.

Select Count(tableA.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)
) as tableA

Jeremy

matkwan
Starting Member

36 Posts

Posted - 2002-07-26 : 11:42:29

Jeremy, it doesn't work, same error message. Sorry for giving you so much trouble.

Matthew

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-26 : 11:46:19

Access a lot of times does not like subqueries. My suggestion to you is to Save your subquery in Access as a Query. then build another Query based on that subquery. You will end up having 2 queries in Access, but less headaches figuring out why access does not like your SQL code built without it's wonderful Design Query helper. Access is very good at helping people that don't know SQL, but can be an utter pain in the $@#$^@& if you actually know SQL.

Maybe someone else here will find something I have missed.

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources