Aggregate function error while using group by clause in SQL

Question

I have table named purchase. It has columns billno, billdate, qty, amount. When I run group by query, it is throwing an error.

Query I used

SELECT 
    BILLNO,
    BILLDATE,
    SUM(QTY) AS SUMQTY,
    SUM(AMOUNT) AS SUMAMOUNT
FROM 
    PURCHASE
GROUP BY 
    BILLNO

This is the error I'm getting - how to get bill wise total amount?

Column 'PURCHASE.BILLDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

`billdate` column has to either be in aggregated (`sum`, `max`, etc.) or included in the `group by` clause. — tarheel, Dec 30 '18 at 05:25
If I include the billdate column in group by I'm not getting the desired result @tarheel — Hari Ram Str, Dec 30 '18 at 05:55
Then you'll need to aggregate it. For more specific help, please provide sample data and expected output. — tarheel, Dec 30 '18 at 06:03

score 2 · Answer 1 · answered Dec 30 '18 at 12:49

The error is pretty obvious. The unaggregated columns in the SELECT of an aggregation query need to match the keys. In your query, BILLDATE is not aggregated and it is not a key.

The simple fix is:

SELECT BILLNO, BILLDATE,
       SUM(QTY) AS SUMQTY,
       SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO, BILLDATE;

If you want only one row per BILLNO -- or if you know that BILLDATE is the same for all BILLNO -- then you can use an aggregation function instead:

SELECT BILLNO, MAX(BILLDATE) as BILLDATE,
       SUM(QTY) AS SUMQTY,
       SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO;

score 0 · Answer 2 · answered Jan 02 '19 at 08:36

According to the official documentation，‘The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list’， it will indicate the cause of your error.

For more details , you can refer to this link and you will see some examples about your issue: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#arguments

There is a great example to explain for you : https://www.codeproject.com/Articles/1110163/%2FArticles%2F1110163%2FSQL-GROUP-By-and-the-Column-name-is-invalid-in-the

score 0 · Answer 3 · answered Jan 13 '19 at 14:35

Here we have to revise the Concept of using the Group By and Order By in A SQL Query.

Remember One thing the name of column that has been came in Order By cannot be used inside aggregate function such as SUM, Average or MAX.

So don't put Same Column Name in Aggregate Function and with Order By too.

Aggregate function error while using group by clause in SQL

3 Answers3

Linked