SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Count Rows within a Query


Count Rows within a Query

Author
Message
CassieF
CassieF
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 7
Wonder if you can help.

this is my select query.
SELECT top 100 percent Form.FormName, Markets.MarketName
FROM Form INNER JOIN
FormMarkets_Link ON Form.FormID = FormMarkets_Link.ptFormID INNER JOIN
Markets ON FormMarkets_Link.ptMarketID = Markets.MarketID CROSS JOIN
FormStatus
WHERE (FormStatus.FormStatusID = 3)

i need to then have the following column headings: Count and Market Name

i have tried a number of differing offerings given by different sites, but i keep getting an error
msg170, level 15, state 1, line 8 Line 8: incorrect syntax near ')'.
using.....
select count (*) from
(SELECT top 100 percent Form.FormName, Markets.MarketName
FROM Form INNER JOIN
FormMarkets_Link ON Form.FormID = FormMarkets_Link.ptFormID INNER JOIN
Markets ON FormMarkets_Link.ptMarketID = Markets.MarketID CROSS JOIN
FormStatus
WHERE (FormStatus.FormStatusID = 3)
)

pls help..
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16640 Visits: 19557
You can have whatever headings you want:

SELECT [Count] = 1, [Market name] = 'Borough Market'

What's important is the column content. Can you explain further? Is it something like this?

SELECT --top 100 percent 
f.FormName,
[Market Name] = m.MarketName,
[Count] = COUNT(*) OVER (PARTITION BY (SELECT NULL))
FROM Form f
INNER JOIN FormMarkets_Link l
ON f.FormID = l.ptFormID
INNER JOIN Markets m
ON l.ptMarketID = m.MarketID
CROSS JOIN FormStatus s
WHERE s.FormStatusID = 3



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
CassieF
CassieF
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 7
thank you, i just tried to execute that and it gave me an incorr syntax error near keyword Over..Doze
it also didnt pink out the first [count] so i dont think that worked either

end result - it needs to count formnames for each marketname.
i dont need to know the formnames.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16640 Visits: 19557
CassieF (2/11/2013)
thank you, i just tried to execute that and it gave me an incorr syntax error near keyword Over..Doze
it also didnt pink out the first [count] so i dont think that worked either

end result - it needs to count formnames for each marketname.
i dont need to know the formnames.


What version of SQL Server are you using, Cassie? This is the 2005 forum section and it should work.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
CassieF
CassieF
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 7
oohh phooey, i am on a higher version than that.. 2008R2
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16640 Visits: 19557
CassieF (2/11/2013)
oohh phooey, i am on a higher version than that.. 2008R2


It works fine on 2008R2, that's what I tested the script on. Are you sure that the server you are connected to is 2008R2, and not just the client (SSMS, probably).

To check, run this: select @@version

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
CassieF
CassieF
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 7
ho hum..

SQL Server 2000 - 8.00.2039 (intel x86)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16640 Visits: 19557
CassieF (2/11/2013)
ho hum..

SQL Server 2000 - 8.00.2039 (intel x86)


Heh no worries. Try this;
SELECT 
MarketName,
COUNT(*)
FROM (
SELECT --top 100 percent
f.FormName,
m.MarketName
FROM Form f
INNER JOIN FormMarkets_Link l
ON f.FormID = l.ptFormID
INNER JOIN Markets m
ON l.ptMarketID = m.MarketID
GROUP BY m.MarketName, f.FormName
--CROSS JOIN FormStatus s
--WHERE s.FormStatusID = 3
) d
GROUP BY MarketName




I can't see how FormStatus is required. You don't need top 100 percent (later versions of SQL Server will ignore it).
Run the inner query as well as the whole query and note any differences.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
CassieF
CassieF
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 7
Thank you, Thank you that works perfectly
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16640 Visits: 19557
You're welcome. Thank you for the feedback.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search