Sql Aggregate function with join

Question

 SELECT ORDERS.ORDERID,
        ORDERS.CUSTOMERID,
        ORDERS.EMPLOYEEID,
        ORDERDETAILS.PRODUCTID,
        ORDERDETAILS.UNITPRICE,
        ORDERDETAILS.QUANTITY,
        COUNT(ORDERS.ORDERID)
 FROM ORDERS
      LEFT JOIN ORDERDETAILS ON ORDERS.ORDERID=ORDERDETAILS.ORDERID
 GROUP BY ORDERDETAILS.ORDERID

ERROR:Column 'ORDERS.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

use group by orders.orderid not group by orderdetails.orderid as it is not included in your select clause and i am completely unsure what you are trying to acheive with this query — Himanshu, Nov 25 '18 at 09:58
This will helps you https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e — Hamza Haider, Nov 25 '18 at 10:01
Welcome to Stack Overflow. Your question doesn't include enough (any) useful detail for us to help you. Check out [How To Ask](https://stackoverflow.com/help/how-to-ask) and the importance of a [Minimal, Complete, Verifiable Example](https://stackoverflow.com/help/mcve). After that, [Start Here](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to edit your question as needed. — Eric Brandt, Nov 25 '18 at 10:02
I suggest getting used to using aliases, and good use of whitespace as well; it'll make your SQL far easier to read for both yourself and others in the future. — Thom A, Nov 25 '18 at 10:23
More on topic; what it is you are trying to achieve here? Adding the full `GROUP BY` is going to give you a count of how many times the same product is listed in a single Order (which considering you have a quantity button, probably means you'll get the value 1). Is this meant to be a count of orders by customers? The key thing when asking a question is to *unsurprisingly*, ask a question; you haven't one just posted your SQL and an error. We can make your SQL work, but we don't know what your goal is. Ask a question, explain your goal. — Thom A, Nov 25 '18 at 10:28
Update your question .. add a proper data sample and the expected result — ScaisEdge, Nov 25 '18 at 10:49

score 0 · Answer 1 · answered Nov 25 '18 at 10:04

0

For using aggregate function selected column will also need to include in group by clause

SELECT 
 ORDERS.ORDERID,ORDERS.CUSTOMERID,ORDERS.EMPLOYEEID,ORDERDETAILS.PRODUCTID,ORD ERDETAILS.UNITPRICE,ORDERDETAILS.QUANTITY,
 COUNT(ORDERS.ORDERID)
 FROM ORDERS LEFT JOIN ORDERDETAILS ON 
 ORDERS.ORDERID=ORDERDETAILS.ORDERID
 GROUP BY ORDERDETAILS.ORDERID,ORDERS.CUSTOMERID,ORDERS.EMPLOYEEID,ORDERDETAILS.PRODUCTID,ORD ERDETAILS.UNITPRICE,ORDERDETAILS.QUANTITY

answered Nov 25 '18 at 10:04

Zaynul Abadin Tuhin

31,407
5
33
63

This'll work, but based on the query the OP has this is probably going to return the value `1` for every row; which is unlikely to be what they want – Thom A Nov 25 '18 at 10:25

score 0 · Answer 2 · answered Nov 25 '18 at 12:25

Presumably, you intend this:

SELECT o.ORDERID, o.CUSTOMERID, o.EMPLOYEEID,
       COUNT(od.ORDERID) as NUM_PRODUCTS
FROM ORDERS o LEFT JOIN
     ORDERDETAILS od
     ON o.ORDERID = od.ORDERID
GROUP BY o.ORDERID;

This produces one row per ORDERID with a count of the number of products (or more specifically orderdetails rows) in each order.

Notes:

All unaggregated columns in the SELECT should be GROUP BY keys.
You don't want to include unaggregated columns from ORDERDETAILS in the SELECT, because then an ORDER might have multiple rows in the result set.
You do want to use table aliases, so the query is easier to write and to read.

Sql Aggregate function with join

2 Answers2