CODEWITHSUNDEEP
sqlt-sqlgroup-by
Morten Lau Torp Nielsen
Morten Lau Torp Nielsen

Reputation: 45

SQL Group By function combined with SUM

I'm getting the error

Column 'Nordlux UK$Item.Description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

when trying to run the SQL statement.

If I remove the SUM function and Group by it works, but I would like to have all the result grouped by my Item number. What have I done wrong in my code?

SELECT
    [Nordlux UK$Item].No_,
    [Nordlux UK$Item].[Description],
    SUM([Nordlux UK$Item Ledger Entry].[Quantity]) AS Int
FROM [Nordlux UK$Item]
JOIN [Nordlux UK$Sales Price]
    ON [Nordlux UK$Item].[No_] = [Nordlux UK$Sales Price].[Item No_]
JOIN [Nordlux UK$Item Ledger Entry]
    ON [Nordlux UK$Item].[No_] = [Nordlux UK$Item Ledger Entry].[Item No_]
WHERE [Nordlux UK$Sales Price].[Sales Code] = 'DUN02'
GROUP BY [Nordlux UK$Item].No_

Upvotes: 3

Views: 465

Answers (3)

M Durk
M Durk

Reputation: 21

When using aggregate functions I always group by all the aggregated columns (i.e. the ones not using aggregate functions like SUM, MAX, MIN, COUNT).

SQL is not a young language, there maybe a reason why you'd want to skip some aggregated columns but I never come across this situation.

Upvotes: 2

Pramod Lolage
Pramod Lolage

Reputation: 21

While using aggregate function in select statement with other columns it is common to use group by and add all fields from select statement in group by except aggregate function.

In your case yo should use: GROUP BY [Nordlux UK$Item].No_, [Nordlux UK$Item].[Description],

If you are using only aggregate function in select statement then there is no need to add group by clause.

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You need to add [Nordlux UK$Item].[Description] in group by clause - as each column of selection list except aggregated function should be group by clause

SELECT
    [Nordlux UK$Item].No_,
    [Nordlux UK$Item].[Description],
    SUM([Nordlux UK$Item Ledger Entry].[Quantity]) AS Int
FROM [Nordlux UK$Item]
JOIN [Nordlux UK$Sales Price]
    ON [Nordlux UK$Item].[No_] = [Nordlux UK$Sales Price].[Item No_]
JOIN [Nordlux UK$Item Ledger Entry]
    ON [Nordlux UK$Item].[No_] = [Nordlux UK$Item Ledger Entry].[Item No_]
WHERE [Nordlux UK$Sales Price].[Sales Code] = 'DUN02'
GROUP BY [Nordlux UK$Item].No_, [Nordlux UK$Item].[Description]

Upvotes: 3

Related Questions