Help with a Sql Query - 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

Help with a Sql Query

Author

Topic

Wraithyme
Starting Member

1 Post

Posted - 2011-11-22 : 20:49:04

Hey all, I'm trying to teach myself some sql and I'm hoping someone could help me figure out how to do this query.

Essentially, I have this table

Consumable Type
Food Meat
Drink Pepsi
Food Popcorn
Food Hamburger
Food Salad
Food Pie
Drink Coke
Drink Beer

Now I run the following query
SELECT DISTINCT a.[Consumable], Count([a.Consumable]) AS counter1
FROM Table1 a
WHERE (((a.[Type])='Coke' Or (a.[Type])='Pepsi'))
GROUP BY a.[Consumable];

Which gives me:
Consumable counter1
Drink 2

What I want to do is create a third table now, that will give me, in addition to what is above, a third column with that total number of times 'Drink' appeared in the original table.

Basically the following

Consumable counter1 FullCount
Drink 2 3

Any ideas on how to make that query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-22 : 20:58:04

[code]
select a.[Consumable],
count(case when a.[Type] in ('Coke', 'Pepsi') then 1 end) as counter1,
count(*) as FullCount
from Table1 a
where a.[Consumable] = 'Drink'
group by a.[Consumable]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 02:34:05

You can avoid warnings on NULL using this


select a.[Consumable],
       sum(case when a.[Type] in ('Coke', 'Pepsi') then 1 else 0 end) as counter1,
       count(*) as FullCount
from   Table1 a
where  a.[Consumable] = 'Drink' 
group by a.[Consumable]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources