Re: group by with multiple selects having different where conditions

On Wed, 24 Apr 2002 08:30:28 -0700 (PDT)
Benjamin Franks <benjamin(at)dzhan(dot)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