Count help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count help

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-02-13 : 14:35:28

Hi All,

I have the following query :

SELECT UPPER(Product_ID) Product_ID, Deductible_Amount,
count(*) Subscribers, State, Type_ID

FROM Insurance with(nolock)
GROUP BY Product_ID, Deductible_Amount, State, Type_ID
ORDER BY Product_ID, Deductible_Amount, State, Type_ID

What I need is to get the count of subscribers who live in NY , count of subscribers from all other (49) states excluding NY.
I am not sure how to go about it..

Thanks for your help,
Petronas

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-13 : 15:30:24

Do a conditional sum. Your state code wouldn't be valid in your query however. For NY it would always be NY and for a total for all other states you have 49 possibilities. Including it would cause your query to sum by state instead of NY and all others.

select product_id,deductible_amount,
sum(case [state] when 'NY' then 1 else 0 end) as NYSubscribers,
sum(case [state] when 'NY' then 0 else 1 end) as OTHERSubscribers
,[type_id]
FROM Insurance
Group by Product_id, deductible_amount,[type_id]

Mike
"oh, that monkey is going to pay"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 15:31:30

[code]Select * from
(SELECT UPPER(Product_ID) Product_ID, Deductible_Amount,
count(*) Subscribers, State, Type_ID

FROM Insurance with(nolock)
Where State = 'NY'
GROUP BY Product_ID, Deductible_Amount, State, Type_ID

UNION

SELECT UPPER(Product_ID) Product_ID, Deductible_Amount,
count(*) Subscribers, State, Type_ID

FROM Insurance with(nolock)
Where State <>'NY'
GROUP BY Product_ID, Deductible_Amount, State, Type_ID
)Z
ORDER BY Product_ID, Deductible_Amount, State, Type_ID[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 00:49:50

quote:
Originally posted by Petronas

Hi All,

I have the following query :

SELECT UPPER(Product_ID) Product_ID, Deductible_Amount,
count(case when [state] ='NY' then subscriber_ID else NULL end) AS NYSubscribers,count(case when [state] <>'NY' then subscriber_ID else NULL end) AS OtherSubscribers, State, Type_ID

FROM Insurance with(nolock)
GROUP BY Product_ID, Deductible_Amount, State, Type_ID
ORDER BY Product_ID, Deductible_Amount, State, Type_ID

What I need is to get the count of subscribers who live in NY , count of subscribers from all other (49) states excluding NY.
I am not sure how to go about it..

Thanks for your help,
Petronas

modify like below

Petronas
Posting Yak Master

134 Posts

Posted - 2009-02-16 : 10:33:01

Hi Visakh16,

Thanks again for providing the solution:

I ran the query :

SELECT UPPER(Product_ID) Product_ID, Deductible_Amount, Carrier,Type_ID ,
count(case when [state] ='NY' then subscriber_ID else NULL end) AS NYSubscribers,
count(case when [state] <>'NY' then subscriber_ID else NULL end) AS OtherSubscribers
FROM Insurance with(nolock)
GROUP BY Product_ID, Deductible_Amount, State, Type_ID
ORDER BY Product_ID, Deductible_Amount, State, Type_ID

It is giving me the following error: Invalid column name 'subscriber_ID'.
How do I go about inserting this column. Excuse my question..

Thanks for your time,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 12:18:01

whats the field that contain subscriber info? replace subscriber_ID with it

Petronas
Posting Yak Master

134 Posts

Posted - 2009-02-16 : 12:47:10

Hi Visakh16,

Thanks for your reply.The columns in the table are :

Order_id,Product_id,Marketing_id,Deductible_Amount,Carrier,Type_id,State.

There is no Subscriber info. Based on state='NY' and state<>'NY' I need to count. The result set should look something like this:

Product_id Deductible_amount Carrier Type_id CountofOtherSubscribers Count0fNYSubscribers

Appreciate your help..

Thanks,
Petronas

Petronas
Posting Yak Master

134 Posts

Posted - 2009-02-16 : 13:04:54

Hi Visakh16,

Thanks for all your help. I got the solution and the right counts by using :

count(case when state='NY' then 1 else 0 end) as CountswithNY

Thanks again for your time,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 02:58:28

quote:
Originally posted by Petronas

Hi Visakh16,

Thanks for all your help. I got the solution and the right counts by using :

count(case when state='NY' then 1 else 0 end) as CountswithNY

Thanks again for your time,
Petronas

nope this is not correct. using 1 & 0 has same effect for count(). use sum() instead

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources