group by

gerardg

All of a sudden i feel clueless about aggregates and group by. I'm trying to do queries like:

select a.*, count(b.id), sum(c.amount)
from a
inner join b
on a.b_id = b.id
left join c
on a.c_id = c.id
group by a.id

In this case, am I supposed to group on each field in 'a'? Should I do this another way? A count and sum query for each item in a? Or an additional query that just gets a.id, count, and sum and then merge the results in code?

[deleted]

GROUP BY will group records by whatever you specify.

This means that all the fields that you don't 'group by' can have multiple values for each unique value of the field that you 'group by'.

for ex; if you have a table of customerids and orderids, one customer can have several orders, so grouping by userid will give several orderids per userid. That means you cannot select orderid, because SQL does not know which of the many values you want.
You have to select an aggregate of orderid, something that will take all values and process them into a new value.

"n this case, am I supposed to group on each field in 'a'? "

No, because that would void the 'group by a.id'. Grouping by all fields is the same as not grouping at all.

The thing is that one value of a.id can theoretically have many possible values for the other fields of a.

You can probably solve this by doing a self-join, where you use a.id in the group by, and then join again with a:

SELECT copy_of_a.*
FROM a blaJOIN b ON bla LEFT JOIN c on bla, a AS copy_of_a WHERE a.id = copy_of_a.id

gerardg

The thing is that one value of a.id can theoretically have many possible values for the other fields of a.<

I think that's what's giving me screwy results. Mysql is sloppy with it's group by. Some queries are taking like 5 seconds to execute and the aggregates have values 10-100 times more than they should. So, if you don't group on fields that can have a non-unique value can any sense be made of the results? Or, as the mysql docs say:

"Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results."

You can probably solve this by doing a self-join, where you use a.id in the group by, and then join again with a:

SELECT copy_of_a.*
FROM a blaJOIN b ON bla LEFT JOIN c on bla, a AS copy_of_a WHERE a.id = copy_of_a.id<

How does this solve it? I think I know what self-joins do, but have never used them or when/why they should be used (have always meant to learn more about them one of these days ;-).