Count of trouble_codes per store_id

Question

Post reply

Count of trouble_codes per store_id

kreskins

Default port

Points: 1429
More actions
June 7, 2006 at 9:23 am

#366366

I am trying to write a t-sql sproc that will give me the store_id, and then a count of each trouble_code logged for that store.
Where I am stumped is: how can I get a count of each trouble_code into the query when there are approx 30 different codes. I would like my output to look like this:
store_id      code_theft     code_fire     code_power...........
store1        2                  1                3..............
store2        1                  3                2................
store3        1                  2                1................
but what I get is this:
store_id     code_theft     code_fire     code_power
store1        1                  0                0..............
store1        1                  0                0..............
store1        0                  1                0..............
store1        0                  0                1..............
store1        0                  0                1..............
store1        0                  0                1..............
store1        2                  1                3..............
store2 etc...etc...etc...
store3 etc...etc...etc...

MY CODE:
CREATE PROCEDURE dbo.sp_trouble_codes
AS
select
table1.store_id,
count(table2.trouble_code)as 'COUNT',
case
when table2.trouble_code='101258' then 'code_theft'
when table2.trouble_code='101259' then 'code_fire'
when table2.trouble_code='102103' then 'code_power'
else 'TOTAL'
END 'SYMPTOM'
from table1, table2
where table1.id = table2.id
group by
table1.store_id, table2.trouble_code
with rollup
order by table1.store_id, table2.trouble_code asc
GO

Thanks For your continued Help.

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

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

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 1

Hi,

Can you provide the script of table with insert statement so that i can check it ?

Regards,

Amit Gupta..

DAVNovak Hall of Fame Points: 3120 More actions · Answer 2

Try using multiple case statements. One case statement for each code.

select table1.store_id,
SUM(Case when table2.trouble_code='101258' then 1 else 0 end) as Code_Theft,
SUM(Case when table2.trouble_code='101259' then 1 else 0 end) as Code_Fire,
etc...

from table1 inner join table2 on ....

group by table1.store_id

Dave N

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Maybe like this?

CREATE PROCEDURE dbo.sp_trouble_codes

AS

select table1.store_id,

count(table2.trouble_code)as 'Total',

sum(case when table2.trouble_code='101258' then 1 else 0 end) 'code_theft',

sum(case when table2.trouble_code='101259' then 1 else 0 end)'code_fire'

sum(case when table2.trouble_code='102103' then 1 else 0 end) 'code_power'

from table1

inner join table2 on table1.id = table2.id

group by table1.store_id

order by table1.store_id

GO

N 56°04'39.16"
E 12°55'05.25"

brendt hess Hall of Fame Points: 3256 More actions · Answer 4

I believe I know what you are attempting to accomplish. This code should do what you need:

CREATE PROCEDURE dbo.usp_trouble_codes

AS

SELECT

table1.store_id,

SUM(CASE WHEN table2.trouble_code = '102158' then 1 else 0 END) as code_theft,

SUM(CASE WHEN table2.trouble_code='101259' then 1 Else 0 END) As code_fire,

SUM(CASE WHEN table2.trouble_code='102103' then 1 else 0 END) As code_power,

else 'TOTAL'

FROM table1

INNNER JOIN table2

ON table1.id = table2.id

group by

table1.store_id

order by table1.store_id

kreskins Default port Points: 1429 More actions · Answer 5

Thanks Brendt, Peter, DAVNovak...all of your similar code DID accomplish what I was looking for. I feel like a newbie that I did not think of using multiple case statements instead of one case statement.

THANKS SO MUCH FOR YOUR ASSISTANCE,

Thanks For your continued Help.