SQL query - group by quesgtion

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL query - group by quesgtion

Author

Topic

jeffbond
Starting Member

16 Posts

Posted - 2008-10-24 : 08:21:19

Hi,

I have a table with 2 columns: item & price

item contains : bread, meat, cheese, beverage
price contains : 2, 6, 1, 4

I need to know the average price per item type (beverage or food)

an example of resultset would be:

Food 3
Beverage 4

What would be the SQL query for this?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-24 : 08:34:45

Do you have a table somewhere that defines what item is what type?

--
Gail Shaw
SQL Server MVP

jeffbond
Starting Member

16 Posts

Posted - 2008-10-24 : 08:46:17

Nope.

I decide the tytpe manually in my query

anything that is not beverage is food.

here is an example of what the item table contains :

name price

bread 2
meat 6
cheese 1
coke 4

I need the average price grouped by food and beverage:

With records above, my result set should be as follow. what is the sql for it?

food 3
beverage 4

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-24 : 08:54:18

select case when item in ('coke'.....) then 'Beverages'
else 'Food' end as [name],avg(price) as price
from table
group by case when item in ('coke') then 'Beverages' else 'Food' end
order by price

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-24 : 10:17:06

[code]DECLARE @Sample TABLE
(
Item VARCHAR(20),
Price SMALLMONEY
)

INSERT @Sample
SELECT 'bread', 2 UNION ALL
SELECT 'meat', 6 UNION ALL
SELECT 'cheese', 1 UNION ALL
SELECT 'beverage', 4

SELECT CASE Item
WHEN 'beverage' THEN Item
ELSE 'food'
END,
AVG(Price)
FROM @Sample
GROUP BY CASE Item
WHEN 'beverage' THEN Item
ELSE 'food'
END[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources