SELECT & GROUP BY Clause

[johnseito]

Hi all,



Does all the fields in the SELECT statement needs to be in the GROUP BY clause regardless of JOINs or not ?

I always thought that what you have in the SELECT, you also need them in the GROUP BY for it to work.

For example:

SELECT a, b, c, d
GROUP BY a, b, c, d

Or can you have fields in the SELECT statement, and in the GROUP BY you have what you have in the SELECT statement and then some extra in the GROUP BY ?

I always thought that what you had in the SELECT, you also need them in the GROUP BY for it to work (not extra).
For example:

SELECT a, b, c, d
GROUP BY a, b, c, d, e, f

In these two scenario, does JOINs make a difference ?

Thank you !!!

[ranman256]

Group By is only needed if you are not summing it.

Group By Name, but SUM(Amt) to get the sum for each Name. (basically its a SHOW this field)

[orange]

Lots of samples and verbiage here.

[johnseito]

Group By is only needed if you are not summing it.

Group By Name, but SUM(Amt) to get the sum for each Name. (basically its a SHOW this field)

I believe GROUP BY is use when you are either counting or summing ! When you are grouping them together,
so you de-dup them.

Thanks Orange, I am looking to your samples.

[johnseito]

Lots of samples and verbiage here.

Orange, I see that example, I understand them.

In both those example, the GROUP BY was use for the field and not the sum or counts, hence, the
SELECT statement has more fields than the GROUP BY clause.

I have an example where the GROUP BY clause field are more than the SELECT statement. I just think is weird and how is that possible, that is
why I ask the questions on top, and hence this thread because that doesn't make sense to me.

From what I know of GROUP BY is that in the GROUP BY statement any field that it reference should be in the SELECT statement with the exception
of either count or sum of.

[johnseito]

Look at this logic, this is just an example, you see the GROUP BY there are a total of 6 fields, while the SELECT is only 4 fields, none of the SELECT are either SUM or COUNT.
Why is this working ? I believe it shouldn't, I believe the number of field of SELECT you have is the number of GROUP BY you should have with the exception to the SELECT having SUM or COUNT.
However in this case is not, we have more GROUP BY fields than we have SELECT fields.

INSERT INTO [tableA] (A, I, C, R)
SELECT tableB.A, tableB.B, tableC.D, tableB.E
FROM (tableB LEFT JOIN tableE ON tableB.A = tableE.C) LEFT JOIN tableC ON tableB.B = tableC.B
GROUP BY tableB.A, tableB.B, tableC.D, tableB.E, tableC.B,  table.C