Обсуждение: group by with multiple selects having different where conditions

I currently am doing an operation where i take data from two tables and
insert into a third summary table.  I'm using the perl dbi to read the
data into my program, work on the data in my program, and then insert back
into the database.  it works fine, but I'm investigating whether I can get
better speed by doing all of the functionality in a single SQL statement
instead.  For the sake of an example, assume the following sequence/loop
(this isn't really how it's done but seems to convey the desired
functonality):

foreach name (DISTINCT table1.name)

    foreach state (DISTINCT table1.state)

        x = SELECT sum(table1.count) from table1,table2
            WHERE table1.id=table2.id AND table2.type='x'

        y = SELECT sum(table1.count) from table1,table2
            WHERE table1.id=table2.id AND table2.type='y'

        z = SELECT sum(table1.count)

        z = z - (x+y)

        INSERT into table3 (name,state,x,y,z)

    }
}

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

    INSERT into table (a,b,c,d,e)
        select (a,b,c) from table where ... group by (a,b)
        select (a,b,d) from table where ... group by (a,b)
        select (a,b,e) from table where ... group by (a,b)


subselects? temp tables? Thanks for any help or ideas.
--Ben

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