SQL Group by Aggregate function

[Sahil123]

Hi

When an aggregate function is used in the select query,do you have to include other fields in the group by statement?

If yes, does it group by the specified order

eg

group by state, name, add

this will group the state first, then name and then add?

is group by the equivalent to countifs or sumifs functions etc in excel?

 

[Kyle123]

Yes, you have to include all non aggregated fields in the group by field. Yes, it refers to the order in which fields are grouped, if all the fields are in your select query, all it really does is sort them. If the group by fields are not in the select query, the output is different

It's a PITA to be honest, but that's how SQL is structured.

 

[Sahil123]

Yes, you have to include all non aggregated fields in the group by field. Yes, it refers to the order in which fields are grouped, if all the fields are in your select query, all it really does is sort them. If the group by fields are not in the select query, the output is different

It's a PITA to be honest, but that's how SQL is structured.

Thank You, that clears alot for me

The reason why i mentioned if the group by worked like Countifs or sumifs is because it looked like it was doing this

sumifs(Sum range, Criteria range1, Criteria1, Criteria range2, Criteria2)

select State, Name, Sum(Balance)
from table1
Group by State, Name

Balance being the Sum range and the group by statements having the criteria range/criteria

Thank you

1 other thing i really struggle with is working with SUB QUERIES and HOW/WHEN to USE CORRELATED SUB QUERIES

Shall i create another thread for this or is someone able to help with this on this thread?

Thank YOu again

 

[Kyle123]

Start a new thread