CODEWITHSUNDEEP
sqlpostgresql
Keeyan
Keeyan

Reputation: 99

Using GROUP BY in SQL with MAX aggregate and selecting an additional column not in GROUP BY statement

SELECT sq.type, sq.cust, MAX(sq.sum)
FROM (
    SELECT C.custid AS cust, P.ptype AS type, SUM(D.qty) AS sum
    FROM Customers C, Orders O, Details D, Products P
    WHERE C.custid = O.ocust and D.ordid = O.ordid and P.pcode = D.pcode    
    GROUP BY cust, type
) as sq
GROUP BY sq.type
;

Hello friends,

My problem is I want to be able to select sq.cust as well as sq.type via the group by function in SQL. When using the MAX aggregate, it should only return 1 tuple anyways which includes sq.type and sq.cust, so how come I'm unable to access it this way? Is there any way to return the customer id (sq.cust) of the max sq.sum for that product type (sq.type)?

Thank you!

Upvotes: 0

Views: 66

Answers (2)

Megadest
Megadest

Reputation: 634

The solution from Gordon Linoff is great, though Pg-specific. To make it slightly more portable one could use window functions, which are available in most major RDBMS now.

For example, in SQL Server:

select C.custid as cust, P.ptype, sum(qty) as sum_qty
    , FIRST_VALUE(sum(qty)) OVER (PARTITION BY ptype ORDER BY SUM(qty) DESC) as max_sum_qty_for_ptype
FROM Customers C JOIN
     Orders O
     ON C.custid = O.ocust JOIN
     Details D
     ON D.ordid = O.ordid JOIN
     Products P
     ON P.pcode = D.pcode

GROUP BY cust, ptype
ORDER BY ptype, SUM(QTY) DESC

You can remove the Sum_qty column, obviously

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

That said, you can use distinct on in Postgres:

SELECT DISTINCT ON (ptype) C.custid as cust, P.ptype AS type, SUM(D.qty) AS sum
FROM Customers C JOIN
     Orders O
     ON C.custid = O.ocust JOIN
     Details D
     ON D.ordid = O.ordid JOIN
     Products P
     ON P.pcode = D.pcode    
GROUP BY cust, ptype
ORDER BY ptype, SUM(d.QTY) DESC

Upvotes: 1

Related Questions