Re: group by with multiple selects having different where conditions

On Wed, 24 Apr 2002 08:30:28 -0700 (PDT)
Benjamin Franks <benjamin@dzhan.com> wrote:

> So, I think that if I were only doing 1 SELECT statement in the inner
> loop, I could use an insert select with a group by, something like:
>
>     INSERT into table3 (name,state,x)
>         SELECT table1.name, table1.state, sum(table1.count)
>         FROM table1,table2 WHERE table1.id=table2.id AND
>         table2.type='x' GROUP BY (table1.name,table1.state)
>
> Is there a way I can do this type of thing when I have multiple select
> statements with different WHERE clauses though? ...something like


Probably, this query will go well and reduce the I/O loss -- especially, accessing
Table1 and Table2 repeatedly.

INSERT INTO
       table3 (name, state, x, y, z)
SELECT
       txy.name,
       txy.state,
       txy.x,
       txy.y,
       tz.z - (txy.x + txy.y) AS z
FROM
       (SELECT t1.name, t1.state,
               SUM(CASE WHEN t2.type='x' THEN t1.count ELSE 0 END) AS x,
               SUM(CASE WHEN t2.type='y' THEN t1.count ELSE 0 END) AS y
         FROM  table1 AS t1, table2 AS t2
         WHERE t1.id = t2.id
         GROUP BY t1.name, t1.state
       ) AS txy,
      (SELECT t3.name, t3.state, SUM(t3.count) AS z
        FROM  table1 AS t3
       -- WHERE ...                  --    if necessary.
        GROUP BY t3.name, t3.state
       ) AS tz
WHERE
       txy.name = tz.name AND txy.state = tz.state
;


Regards,
Masaru Sugawara