From: | Grant Masan <grant(dot)massan(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | slow count(CASE) query |
Date: | 2009-10-29 18:27:33 |
Message-ID: | c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi all,
I have this kind of query that I need to do, yes my query is giving right
answers now but it is long and slow. I am now asking you that if
you have another solution for my query to make that more smarter ! Hope you
can help me with this !
select '000100' as length, sum(ship1) as ship1 ,sum(ship2) as
ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM
(select
count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1,
count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2,
count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3,
count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4,
count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5
FROM school_proj_boat where length <100
GROUP BY type
ORDER BY type) as koo
UNION ALL
select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as
ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM
(select
count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1,
count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2,
count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3,
count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4,
count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5
FROM school_proj_boat where length between 100 and 200
GROUP BY type
ORDER BY type) as koo
UNION ALL
select '200300' as length, sum(ship1) as ship1 ,sum(ship2) as
ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM
(select
count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1,
count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2,
count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3,
count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4,
count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5
FROM school_proj_boat where length between 200 and 300
GROUP BY type
ORDER BY type) as koo
UNION ALL
select '300999' as length, sum(ship1) as ship1 ,sum(ship2) as
ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM
(select
count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1,
count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2,
count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3,
count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4,
count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5
FROM school_proj_boat where length >300
GROUP BY type
ORDER BY type) as koo
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2009-10-29 21:49:01 | Re: slow count(CASE) query |
Previous Message | Gerardo Herzig | 2009-10-29 18:14:04 | Re: slow count(CASE) query |