Using CASE - WHEN to create new column

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

Using CASE - WHEN to create new column

Author

Topic

Tomb
Starting Member

10 Posts

Posted - 2012-02-02 : 17:49:15

Hello,

I'm trying to return a table that just has three columns, 'type', 'date', and 'value' and rows for different countries using the following query:

select case when right(locale,2)='US' then 'US'
when right(locale,2)='GB' then 'GB'
when right(locale,2)='CA' then 'CA'
when right(locale,2)='AU' then 'AU'
when right(locale,2)='FR' then 'FR'
when right(locale,2)='DE' then 'DE' else 'ROW' end as 'type',
date_trunc('day', session_start) as 'date', (sum(price)) as 'value'
from database.table
where date_trunc('day', session_start) = '2012-01-30'
group by date_trunc('day', session_start), type;

I'm getting "ERROR: column "table.locale" must appear in the GROUP BY clause or be used in an aggregate function". I understand why I'm getting this error but am not sure how to restructure the query to get what I want. 'Locale' contains messy country code information in the database.table and I want to use that to make a more aggregated 'type' column in the new table. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 18:16:52

[code]
select type,date,sum(price) as value
from
(
select case when right(locale,2)='US' then 'US'
when right(locale,2)='GB' then 'GB'
when right(locale,2)='CA' then 'CA'
when right(locale,2)='AU' then 'AU'
when right(locale,2)='FR' then 'FR'
when right(locale,2)='DE' then 'DE' else 'ROW' end as 'type',
date_trunc('day', session_start) as 'date',price
from database.table
where date_trunc('day', session_start) = '2012-01-30'
)
group by type,date
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tomb
Starting Member

10 Posts

Posted - 2012-02-02 : 18:37:09

That was fast, thanks. I'm now getting an "ERROR: subquery in FROM must have an alias" even when I change it to "from database.table as a"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 19:40:31

[code]
select type,date,sum(price) as value
from
(
select case when right(locale,2)='US' then 'US'
when right(locale,2)='GB' then 'GB'
when right(locale,2)='CA' then 'CA'
when right(locale,2)='AU' then 'AU'
when right(locale,2)='FR' then 'FR'
when right(locale,2)='DE' then 'DE' else 'ROW' end as 'type',
date_trunc('day', session_start) as 'date',price
from database.table
where date_trunc('day', session_start) = '2012-01-30'
)a
group by type,date
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tomb
Starting Member

10 Posts

Posted - 2012-02-03 : 12:51:20

Ah that's it, works now. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources