Grant Masan wrote: > > 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 ... > FROM school_proj_boat where length between 100 and 200 > GROUP BY type > ORDER BY type) as koo > > UNION ALL ...
First thing is to ditch the UNION ALLs. You're basically repeating the same query. Create a lookup table: length_codes (code, min_length, max_length) Data: ('100100', 0, 99), ('100200', 100, 199), ... SELECT length_code AS length, sum... FROM ( SELECT lc.code AS length_code, count(case)... FROM school_proj_boat spb, length_codes lc WHERE spb.length BETWEEN lc.min_length AND lc.max_length ) AS koo ; It's easy to forget that you can join against a table using any condition, it doesn't have to be equality. Here we use BETWEEN to replace our UNIONs. You'll want a unique constraint on length_codes.code and you should really write a custom trigger to make sure none of the min_length..max_length ranges overlap. In practice, you're probably only setting this table up once so might not bother. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql