CODEWITHSUNDEEP
mysqlgroup-by
muazfaiz
muazfaiz

Reputation: 5021

MYSQL GROUP BY clause adding a non GROUP BY column

I have a table in MySQL and I want to do some group by operation. So here is my table

enter image description here

The expected result should be the sum of prices group by bag_no but the product_id should be the mainproduct='Y'. So the result should be like this

enter image description here

I can do the following

SELECT bag_no, sum(price) AS total_price 
FROM myTable
GROUP BY bag_no

But this will not allow me to add product_id in the table which I also want. How can we do that?

Upvotes: 0

Views: 34

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521684

One canonical way to do this involves joining to a subquery which finds the sums:

SELECT
    t1.bag_no,
    t1.product_id,
    t2.total_price
FROM myTable t1
INNER JOIN
(
    SELECT bag_no, SUM(price) AS total_price
    FROM myTable
    GROUP BY bag_no
) t2
    ON t1.bag_no = t2.bag_no
WHERE
    t1.mainproduct = 'Y';

With MySQL 8+, which supports analytic functions, we can slightly simplify the above query:

WITH cte AS (
    SELECT
        t1.bag_no,
        t1.product_id,
        SUM(price) OVER (PARTITION BY t1.bag_no) total_price,
        t1.mainproduct
    FROM myTable t1
)

SELECT bag_no, product_id, total_price
FROM cte
WHERE mainproduct = 'Y';

Upvotes: 2

Related Questions