I am studying for my Final exam in CIS 365 and we have always learned that you normally, a good majority of the time (ALWAYS), ONLY use 'Group By' and 'Having' clauses when an aggregate function is present in your query. However I was looking over some sample code I found on the internet and there was a query that read
'/*Query 5. Customer payment info including all relevant data. */
CREATE VIEW CustomerPaymentInfo
as SELECT C.CustomerID, C.CustomerFName as 'Customer First Name', C.CustomerLName as 'Customer Last Name', C.CreditAuthorization as 'Credit Authorized?', C.CreditLimit as 'Credit Limit'
FROM Customer C
GROUP BY C.CustomerID, C.CustomerFName, C.CustomerLName, C.CreditAuthorization, C.CreditLimit;
SELECT * FROM CustomerPaymentInfo;'
If there is no aggregate in this code why is there a 'group by' clause?! Please help ASAP
You can use GROUP BY even if you don't have an aggregate in the SELECT or HAVING clause. You can add an aggregate function in the select or having clauses, but if you have no need for such aggregates, SQL Server is not going to complain.
Logically using a GROUP BY without any aggregate functions is equivalent to using the SELECT DISTINCT and omitting the GROUP BY clause.
create table foo(bar int, fum int)
select * from foo
group by bar
produces:
Msg 8120, Level 16, State 1, Line 2
Column 'foo.fum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Of course the OP's query has the same columns in the select and group by, which is the same as distinct
FWIW you can omit aggregates in MySql, but its not the same as distinct. The result you get for a non-aggregated column is one of the results in the group, but you can't predict which one (got burned by this)