Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id CE1B9633D49 for ; Thu, 29 Oct 2009 15:27:46 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 39885-08 for ; Thu, 29 Oct 2009 18:27:33 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f218.google.com (mail-ew0-f218.google.com [209.85.219.218]) by mail.postgresql.org (Postfix) with ESMTP id 550066333C9 for ; Thu, 29 Oct 2009 15:27:35 -0300 (ADT) Received: by ewy18 with SMTP id 18so2805410ewy.19 for ; Thu, 29 Oct 2009 11:27:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=QN132cahQZkPhyRGO0aVrTU1tWAVT6utfUKduZozG/A=; b=oCUIfBKLv/5EdfNAG5aukZvtD21Uw0GXcEbjJGVi/JZ/i19hGgnjXrNwkkuUEEuu0a mlTFOE0EFQPdD5GSsSg35WvPscRNEgjB1WNwDJxpRtXfK4ezoLk3Zdpv+OhOtE/6MrgL 8ue5gzFNzEsGTRXGujJzwqjWPcycgA8ZRBark= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=tf1UE4cEqsO9tRJGF7Xn/alls7Zethv6wWfiRcW8JB0sOEpITCGybPneAnPJVLo2DW o6etJ+El6KBHTBSrZTLlYmiqo8zCQYyM2b9AjlbtKC3BI/sGVx7q+2iwJMZUL2kiE/L2 KsYUZlvJizOT3y4D046VrYOQTAmiPDraD28dI= MIME-Version: 1.0 Received: by 10.216.91.14 with SMTP id g14mr154517wef.115.1256840853399; Thu, 29 Oct 2009 11:27:33 -0700 (PDT) Date: Thu, 29 Oct 2009 20:27:33 +0200 Message-ID: Subject: slow count(CASE) query From: Grant Masan To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d99b259b2ae90477171000 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/138 X-Sequence-Number: 33337 --0016e6d99b259b2ae90477171000 Content-Type: text/plain; charset=ISO-8859-1 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 --0016e6d99b259b2ae90477171000 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi= all,=A0

I have this kind of query that I need to do, yes my query i= s giving right answers now but it is long and slow. I am now asking you tha= t if=A0
you have another solution for my query to make that more smarter ! Hope you= can help me with this !=A0


select '000100' as length, s= um(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as s= hip4,sum(ship5) as ship5 FROM=A0
(select=A0
count(CASE WHEN (type between 40 and 49) =A0THEN 1 ELSE NULL = END) as ship1,=A0
count(CASE WHEN (type between 60 and 69) THEN 1 ELSE N= ULL END) as ship2,=A0
count(CASE WHEN (type between 70 and 79) THEN 1 EL= SE NULL END) as ship3,=A0
count(CASE WHEN (type between 80 and 89) =A0THEN 1 ELSE NULL END) as ship4,= =A0
count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5=A0
F= ROM school_proj_boat where length <100=A0
GROUP BY type=A0
ORDER B= Y type) as koo=A0

UNION ALL=A0

select '100200' as length, sum(ship1) as sh= ip1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5)= as ship5 FROM=A0
(select=A0
count(CASE WHEN (type between 40 and 49)= =A0THEN 1 ELSE NULL END) as ship1,=A0
count(CASE =A0WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2,= =A0
count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as sh= ip3,=A0
count(CASE =A0WHEN (type between 80 and 89) =A0THEN 1 ELSE NULL = END) as ship4,=A0
count(CASE =A0WHEN (type >90) THEN 1 ELSE NULL END) as ship5=A0
FROM = school_proj_boat where length between 100 and 200=A0
GROUP BY type=A0ORDER BY type) as koo=A0

UNION ALL=A0

select '200300'= ; as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,s= um(ship4) as ship4,sum(ship5) as ship5 FROM=A0
(select=A0
count(CASE WHEN (type between 40 and 49) =A0THEN 1 ELSE NULL = END) as ship1,=A0
count(CASE =A0WHEN (type between 60 and 69) THEN 1 ELS= E NULL END) as ship2,=A0
count(CASE WHEN (type between 70 and 79) THEN 1= ELSE NULL END) as ship3,=A0
count(CASE =A0WHEN (type between 80 and 89) =A0THEN 1 ELSE NULL END) as shi= p4,=A0
count(CASE =A0WHEN (type >90) THEN 1 ELSE NULL END) as ship5= =A0
FROM school_proj_boat where length between 200 and 300=A0
GROUP B= Y type=A0
ORDER BY type) as koo=A0

UNION ALL=A0

select '300999'= as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,su= m(ship4) as ship4,sum(ship5) as ship5 FROM=A0
(select=A0
count(CASE W= HEN (type between 40 and 49) =A0THEN 1 ELSE NULL END) as ship1,=A0
count(CASE =A0WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2,= =A0
count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as sh= ip3,=A0
count(CASE =A0WHEN (type between 80 and 89) =A0THEN 1 ELSE NULL = END) as ship4,=A0
count(CASE =A0WHEN (type >90) THEN 1 ELSE NULL END) as ship5=A0
FROM = school_proj_boat where length >300=A0
GROUP BY type=A0
ORDER BY ty= pe) as koo --0016e6d99b259b2ae90477171000--