Home > PostgreSQL > Using CASE with SELECT

Using CASE with SELECT

Today I came across an interesting example, another team at our office working with a bug tracking software needed to group the data within one of the tables on the basis of a specific component and I was able help them out with the query for doing so. It can be explained in an easier way using the dummy data…

The table and the data can be represented as:

create table testsample (ticketid int, component text, keywords text);

insert into testsample values (1, ‘JTA’, ‘Runtime’);
insert into testsample values (2, ‘JTA’, ‘MSG’);
insert into testsample values (3, ‘JTA’, ‘MSG’);
insert into testsample values (4, ‘JTA’, ‘Deploy’);
insert into testsample values (5, ‘EJB’, ‘Runtime’);
insert into testsample values (6, ‘EJB’, ‘Deploy’);
insert into testsample values (7, ‘J2EE’, ‘Runtime’);

Now we had to represent the data with groups of JTA, EJB and J2EE and then give the counts of MSG, Runtime and Deploy for each component type… SELECT with CASE came handy in this scenario as I ended up doing for a quick solution:

SELECT
component,

CASE WHEN component = ‘JTA’
THEN (select count(1) from testsample where keywords = ‘Runtime’ and component = ‘JTA’)
WHEN component = ‘EJB’
THEN (select count(1) from testsample where keywords = ‘Runtime’ and component = ‘EJB’)
WHEN component = ‘J2EE’
THEN (select count(1) from testsample where keywords = ‘Runtime’ and component = ‘J2EE’)
ELSE 0
END AS Runtime
,
CASE WHEN component = ‘JTA’
THEN (select count(1) from testsample where keywords = ‘Deploy’ and component = ‘JTA’)
WHEN component = ‘EJB’
THEN (select count(1) from testsample where keywords = ‘Deploy’ and component = ‘EJB’)
WHEN component = ‘J2EE’
THEN (select count(1) from testsample where keywords = ‘Deploy’ and component = ‘J2EE’)
ELSE 0
END AS Deploy
,
CASE WHEN component = ‘JTA’
THEN (select count(1) from testsample where keywords = ‘MSG’ and component = ‘JTA’)
WHEN component = ‘EJB’
THEN (select count(1) from testsample where keywords = ‘MSG’ and component = ‘EJB’)
WHEN component = ‘J2EE’
THEN (select count(1) from testsample where keywords = ‘MSG’ and component = ‘J2EE’)
END AS MSG
FROM testsample
group by component

And the output was…

output

Update:

David Fetter came up with a much better and cleaner solution…

SELECT
component,
SUM(CASE keywords WHEN ‘Runtime’ THEN 1 ELSE 0 END) AS Runtime,
SUM(CASE keywords WHEN ‘Deploy’ THEN 1 ELSE 0 END) AS Deploy,
SUM(CASE keywords WHEN ‘MSG’ THEN 1 ELSE 0 END) AS MSG
FROM
testsample
GROUP BY component;


Shoaib Mir
shoaibmir[@]gmail.com

Advertisements
Categories: PostgreSQL Tags: ,
  1. March 10, 2009 at 6:48 pm

    Maybe I’m missing something really obvious here, but wouldn’t this be simpler, faster, and do the same thing?

    SELECT
    component,
    SUM(CASE keywords WHEN ‘Runtime’ THEN 1 ELSE 0 END) AS Runtime,
    SUM(CASE keywords WHEN ‘Deploy’ THEN 1 ELSE 0 END) AS Deploy,
    SUM(CASE keywords WHEN ‘MSG’ THEN 1 ELSE 0 END) AS MSG
    FROM
    testsample
    GROUP BY component;

  2. March 10, 2009 at 7:00 pm

    oh thats exactly what we wanted but I just didnt think about it as did it like the way that worked for me in the first try 🙂

    Thanks for the solution, this is much cleaner and faster.

  3. orcus
    March 10, 2009 at 8:00 pm

    You can also use crosstab query from tabfunc contrib module – it’s good even used tables are quite big:
    select * from crosstab(
    ‘select component,keywords, count(*) from testsample group by component,keywords order by component,keywords’,
    ‘select distinct keywords from testsample order by keywords’
    ) as t (component text, “Deploy” bigint, “MSG” bigint,”Runtime” bigint);

  4. greg
    March 10, 2009 at 8:04 pm

    sometimes with more data, it is actually better to create temporary lookup table, or something – and a view 😉
    Will be also much faster on large set of data, than bunch of CASEs

  5. March 10, 2009 at 8:53 pm

    @Orcus: I do agree with that but like they didnt have the contrib module installed so never thought about it 🙂

    @Greg: Very true, but in this specific case they just had around 1000 rows for which performance was not really a problem and they were not expecting it to grow above that.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: