Group by in combination with case

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 2000 Forums

Transact-SQL (2000)

Group by in combination with case

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-17 : 07:24:24

Henrik writes "Hi,

I have made a query in SQL Server Version 8 (MS Win2k OS) in order to select the number of customer orders and costs in USA and in other countries during September 2003 - here goes:

select distinct(cu.name), count(distinct(t.orderref)), sum(ordercost), 'area' =
case
when t.country ='US' then 'USA'
when t.country <> 'US' then 'Other'
end
from ordertable t
left outer join customer cu on t.customerid=cu.id
where t.orderdate >='2003-09-01'
and t.orderdate<='2003-09-30 23:59:59'
group by cu.name
order by cu.name

The error message from SQL is:
Server: Msg 8118, Level 11, State 1, Line 1
Column 't.country' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

If I add t.country in the group by row, then the query is processed but the result is grouped on t.country, which I don't want. Is it possible to correct the query in order to group it on cu.name and 'area' (or on cu.name only)?

Cheers!
Henrik"

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-17 : 08:15:41

Haven't tried this but

(you don't need DISTINCT on a selected element included in the group by)

SELECT name, 
  case
    when country ='US' then 'USA'
    ELSE 'Other'
  end AS area,
OrderTotal,
OrderSum
FROM (
  select DISTINCT(cu.name, t.country count(distinct(t.orderref)) AS OrderTotal, sum(ordercost) as OrderSum,  
  case
  when t.country ='US' then 'USA'
  when t.country <> 'US' then 'Other'
  end AS area
  from ordertable t
  left outer join customer cu on t.customerid=cu.id
  where t.orderdate >='2003-09-01'
  and t.orderdate<='2003-09-30 23:59:59'
  group by cu.name, t.country
) X
order by name

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-17 : 08:46:05

Put the case statement in the group by clause:

select	distinct(cu.name)
	, count(distinct(t.orderref))
	, sum(ordercost)
	, 'area' = Case
		when t.country ='US' then 'USA'
		when t.country <> 'US' then 'Other'
		end

from ordertable t
left outer join customer cu on t.customerid=cu.id
where t.orderdate >='2003-09-01' 
and t.orderdate<='2003-09-30 23:59:59' 
group by cu.name,(Case
		when t.country ='US' then 'USA'
		when t.country <> 'US' then 'Other'
		end)
order by cu.name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources