CODEWITHSUNDEEP
sqlsql-serverfunctionaggregate
Toph Hughes
Toph Hughes

Reputation: 111

Group by/aggregate error - SQL

The SQL I am using for this query is as follows:

SELECT p.publisherID, p.publisherName, month(o.orderDate), sum(ol.quantity)
FROM Orders o, orderLine ol, Publisher p, Book b
WHERE ol.orderNum = o.orderNum
And ol.isbn is not null
And b.isbn=ol.isbn
And p.publisherID=b.publisherID
Group By p.publisherID, p.publisherName;

I get this error: 

Msg 8120, Level 16, State 1, Line 1
Column 'Orders.orderDate' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Now as you can see, the orderDate column is in fact in a function, but why would I still get this error? The only thing I can think of is that the month() function shows (expression datetime) when I hover over it, and the orderDate is just a "date" datatype. I dunno if that is the problem.

Upvotes: 0

Views: 5251

Answers (5)

avdhesh maurya
avdhesh maurya

Reputation: 115

Some of the Rules for Group BY Clause:

  1. Only items allowed in the select list of query with group by clause are,

    • Expressions in Group BY
    • Aggregate Functions (SUM, COUNT, MAX, Min, AVG)
  2. Expressions in the group by clause need not to be included in select statement

As already explained by others, Month is not an aggregate function but a scalar one.

Upvotes: 1

Jakub Dering
Jakub Dering

Reputation: 76

Month is not an aggregate function, you need to include this column in your group by clause: 

SELECT p.publisherID, p.publisherName, month(o.orderDate), sum(ol.quantity)
FROM Orders o, orderLine ol, Publisher p, Book b
WHERE ol.orderNum = o.orderNum
And ol.isbn is not null
And b.isbn=ol.isbn
And p.publisherID=b.publisherID
Group By p.publisherID, p.publisherName, month(o.orderDate);

Upvotes: 0

shogun_kub
shogun_kub

Reputation: 1

If I understand you correctly, you got confused with simple scalar function and aggregate function. Aggregate function takes a set of arguments(field values from many rows) and produce one result, while simple scalar function produce result for each argument it takes. In your case, for a set

orderDate      quantity 
01/01/2017     2        
02/01/2017     2
03/01/2017     2

SELECT SUM(quantity) FROM table

Will produce only one value - 6. While

SELECT MONTH(orderDate) FROM table

Will produce three rows of values:

1
2
3

That's the difference between simple scalar and aggregate functions: aggregate functions can change number of rows in the result, while for simple function there will be equal number of rows in the input and output of the function. Thus, you cannot recieve one result per group of rows, as you do for aggregate function, so you must add field with simple function to GROUP BY clause.

Upvotes: 0

MarkD
MarkD

Reputation: 1063

SELECT p.publisherID, p.publisherName, month(o.orderDate), sum(ol.quantity)
FROM Orders o, orderLine ol, Publisher p, Book b
WHERE ol.orderNum = o.orderNum
And ol.isbn is not null
And b.isbn=ol.isbn
And p.publisherID=b.publisherID
Group By p.publisherID, p.publisherName, month(o.orderDate);

This would return

+-----+------+-------+-------+
| 1   |Bookco|January| 12    |
| 1   |Bookco|Febuary| 6     |
| 2   |NextBk|January| 2     |
+-----+------+-------+-------+

Without the order date in the group by the compiler doesn't know how to handle a variety of months coming out for each publisher.

Upvotes: 3

r0xette
r0xette

Reputation: 908

As the error states, you have to add Orders.orderDate in Group By list.

Upvotes: 0

Related Questions