Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count Rows within a Query Expand / Collapse
Author
Message
Posted Monday, February 11, 2013 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 3:16 AM
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..
Post #1418462
Posted Monday, February 11, 2013 8:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1418495
Posted Monday, February 11, 2013 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 3:16 AM
Points: 6, Visits: 7
thank you, i just tried to execute that and it gave me an incorr syntax error near keyword Over..
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.
Post #1418510
Posted Monday, February 11, 2013 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
CassieF (2/11/2013)
thank you, i just tried to execute that and it gave me an incorr syntax error near keyword Over..
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
Post #1418511
Posted Monday, February 11, 2013 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 3:16 AM
Points: 6, Visits: 7
oohh phooey, i am on a higher version than that.. 2008R2
Post #1418513
Posted Monday, February 11, 2013 9:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1418518
Posted Monday, February 11, 2013 9:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 3:16 AM
Points: 6, Visits: 7
ho hum..

SQL Server 2000 - 8.00.2039 (intel x86)
Post #1418521
Posted Monday, February 11, 2013 9:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1418527
Posted Monday, February 11, 2013 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 3:16 AM
Points: 6, Visits: 7
Thank you, Thank you that works perfectly
Post #1418532
Posted Monday, February 11, 2013 9:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1418533
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse