Count Rows within a Query

Question

Post reply

Count Rows within a Query

CassieF

SSC Rookie

Points: 42
More actions
February 11, 2013 at 8:15 am

#269934

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

CassieF SSC Rookie Points: 42 More actions · Answer 2

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 SSC Guru Points: 186127 More actions · Answer 3

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

CassieF SSC Rookie Points: 42 More actions · Answer 4

oohh phooey, i am on a higher version than that.. 2008R2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

CassieF SSC Rookie Points: 42 More actions · Answer 6

CassieF

SSC Rookie

Points: 42

February 11, 2013 at 9:33 am

#1586647

ho hum..

SQL Server 2000 - 8.00.2039 (intel x86)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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

CassieF SSC Rookie Points: 42 More actions · Answer 8

CassieF

SSC Rookie

Points: 42

February 11, 2013 at 9:51 am

#1586657

Thank you, Thank you that works perfectly

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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