I haven't used SQL in many years and never used it that much, but I thoughtI new how to do this. Apparently I forgot if I ever knew.

I have data that looks something like this:

key side park stat
cat front aaa 1
cat front bbb 2
cat front xxx 4
cat back aaa 8
cat back bbb 16
cat back xxx 32
dog front aaa 64
dog front bbb 128
dog front xxx 256
dog back aaa 512
dog back bbb 1024
dog back xxx 2048


I need to move it to a new table, summing on side, and putting the x data in a new column rather than its own row.

key park stat xstat
cat aaa 9 36
cat bbb 18 36
dog aaa 576 2304
dog bbb 1152 2304

I thought I had it with an self join, distinct, group by... but my numbers were twice what they should have been. When I took out the SUM(), there were twice as many rows as there should have been, though each stat was only matched with any xstat once--the rows were not duplicates of each other.

I could try to type what I thought would work, but in the actual situation,key is several columns, there are several stat columns, each with its own xstat, and I'll have to join a 3rd instance of the original table to pull out what we could call a ystat for each stat, where one part of the key has a special value. I think if I understand how to what I've asked above, I'llget the rest of it and I don't want to confuse the issue by perhaps mistyping my proposed SELECT when I simplify it to the example above. I've been at this all day and my eyes and mind are bleary.

On Sun, 25 Dec 2011 00:46:03 -0800 (PST), dvdavins (AT) pobox (DOT) com wrote:
Have you read up on DISTINCT yet? And you're probably going to want to
do some tricks with subqueries to materialize you aggregate columns if
you get much more complicated, which the next paragraph made sound
likely.

--
Give a man a fish and you feed him for a day; give him a freshly-charged
Electric Eel and chances are he won't bother you for anything ever
again.
-- Tanuki

Got it. Using a slightly different example, which gets rid of "key" as a column name:

select * from t1 (Forgive the quotes; that's just the way I exported the result set.)

"pet" "side" "day" "stat"
"cat" "back" "fri" "16"
"cat" "back" "mon" "8"
"cat" "back" "sat" "32"
"cat" "front" "fri" "2"
"cat" "front" "mon" "1"
"cat" "front" "sat" "4"
"dog" "back" "fri" "1024"
"dog" "back" "mon" "512"
"dog" "back" "sat" "2048"
"dog" "front" "fri" "128"
"dog" "front" "mon" "64"
"dog" "front" "sat" "256"


SELECT DISTINCT
a.pet, a.day, SUM(a.stat), SUM(b.stat)
FROM t1 a JOIN t1 b USING (pet, side)
WHERE a.day != "sat" AND b.day = "sat"
GROUP BY a.pet, a.day

"pet" "day" "sum(a.stat)" "sum(b.stat)"
"cat" "fri" "18" "36"
"cat" "mon" "9" "36"
"dog" "fri" "1152" "2304"
"dog" "mon" "576" "2304"

I had been using DISTINCT, but I'd had my USING clause wrong. Thank you.