Stored proc to count rows in a table based on a foreigh key different values

Question

Stored proc to count rows in a table based on a foreigh key different values

dubem1-878067

Ten Centuries

Points: 1044
More actions
January 28, 2009 at 5:39 pm

#196662

I always got good answer while posting here but this time, I have a situation which I think will ask you the best of your knowledge.
I have a "Table A" with a foreign key on a Status table. The Status table contains 6 differents status. So the FK idStatus in table A can contains 6 differents value (let's call them 1, 2, 3, 4, 5, 6).
I need a Stored proc which return how many rows of each status in Table A
So I need the count of rows idStatus = 1, the count of rows idStatus = 2 and so on.
Can I do this with a single stored proc?
Thank you
Martin

Viewing 6 posts - 1 through 5 (of 5 total)

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

Vladan SSC-Insane Points: 22072 More actions · Answer 1

dubem1 (1/28/2009)
I have a "Table A" with a foreign key on a Status table. The Status table contains 6 differents status.
I need a Stored proc which return how many rows of each status in Table A

Where is the problem? I must have misunderstood the question, because it seems to be a simple select. Do you have any restrictions, like that the resultset must have one row, or similar? Please be more specific if that's the case. If not, why not do this :

SELECT idstatus, COUNT(*)

FROM TableA

GROUP BY idstatus

dubem1-878067 Ten Centuries Points: 1044 More actions · Answer 2

In gact I need each count in variables for further treatment.

I know I can do 6 Select statements

Set @Status1 = Select count(*) from TableA Where idStatus = 1

Set @Status2 = Select count(*) from TableA Where idStatus = 2

etc.

But It looks to me not efficient, whate is I have 12 status? I have to do 12 Select? There must be an more efficient way.

I would like to do all the count within a single Select statetement return 1 row with 6 columns. (which then I can set to 6 variables)

Column 1 would be the number of rows of the table in which idStatus =1 second column number of rows of the table in which idStatus =2 and so on.

I hope it's more clear now

thank you

Martin

Lowell SSC Guru Points: 323518 More actions · Answer 3

to do that, you need a case statement for each possible value:

[font="Courier New"]

SELECT

SUM(CASE WHEN idstatus = 1 THEN 1 ELSE 0 END) AS STATUS1,

SUM(CASE WHEN idstatus = 2 THEN 1 ELSE 0 END) AS STATUS2,

SUM(CASE WHEN idstatus = 3 THEN 1 ELSE 0 END) AS STATUS3,

SUM(CASE WHEN idstatus = 4 THEN 1 ELSE 0 END) AS STATUS4,

SUM(CASE WHEN idstatus = 5 THEN 1 ELSE 0 END) AS STATUS5,

SUM(CASE WHEN idstatus = 6 THEN 1 ELSE 0 END) AS STATUS6

FROM TableA

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dubem1-878067 Ten Centuries Points: 1044 More actions · Answer 4

oh great thank you

Do I have to worry about performance with this kind of Select Statement?

Thanks

Martin

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

Surprisingly, no:

DROP TABLE #TableA

CREATE TABLE #TableA (idstatus INT)

INSERT INTO #TableA (idstatus)

SELECT TOP 500000 CAST(LEFT(number, 1) AS INT)

FROM dbo.Numbers

WHERE CAST(LEFT(number, 1) AS INT) < 7

ORDER BY NEWID()

----------------------------------------------------------------------------

-- Lowell's

SELECT

SUM(CASE WHEN idstatus = 1 THEN 1 ELSE 0 END) AS STATUS1,

SUM(CASE WHEN idstatus = 2 THEN 1 ELSE 0 END) AS STATUS2,

SUM(CASE WHEN idstatus = 3 THEN 1 ELSE 0 END) AS STATUS3,

SUM(CASE WHEN idstatus = 4 THEN 1 ELSE 0 END) AS STATUS4,

SUM(CASE WHEN idstatus = 5 THEN 1 ELSE 0 END) AS STATUS5,

SUM(CASE WHEN idstatus = 6 THEN 1 ELSE 0 END) AS STATUS6

FROM #TableA

-----------------------------------------------------------------------------

-- Another way

DECLARE @STATUS1 INT, @STATUS2 INT, @STATUS3 INT, @STATUS4 INT, @STATUS5 INT, @STATUS6 INT

SELECT @STATUS1 = 0, @STATUS2 = 0, @STATUS3 = 0, @STATUS4 = 0, @STATUS5 = 0, @STATUS6 = 0

SELECT

@STATUS1 = @STATUS1 + CASE WHEN idstatus = 1 THEN [Rows] ELSE 0 END,

@STATUS2 = @STATUS2 + CASE WHEN idstatus = 2 THEN [Rows] ELSE 0 END,

@STATUS3 = @STATUS3 + CASE WHEN idstatus = 3 THEN [Rows] ELSE 0 END,

@STATUS4 = @STATUS4 + CASE WHEN idstatus = 4 THEN [Rows] ELSE 0 END,

@STATUS5 = @STATUS5 + CASE WHEN idstatus = 5 THEN [Rows] ELSE 0 END,

@STATUS6 = @STATUS6 + CASE WHEN idstatus = 6 THEN [Rows] ELSE 0 END

FROM (SELECT idstatus, COUNT(*) AS [Rows]

FROM #TableA

GROUP BY idstatus) d

SELECT @STATUS1, @STATUS2, @STATUS3, @STATUS4, @STATUS5, @STATUS6

^{“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