Question 21680 in Mysql Question posted in
The official MySQL documentation can be found here.

query to combine rows with same col value into one row with new cols based on a condition in sql – Mysql

I have data in my table as:

sku grade price
iphone A 700
iphone B 600
samsung A 200

I want to merge the rows based on the sku col and have cols as A_price and B_price based on the grade value.

The result should be:

sku A_price B_price
iphone 700 600
samsung 200 null

I have tried the below query:

SELECT 
 CASE WHEN grade = 'A' THEN price end as A_price,
 CASE WHEN grade = 'B' THEN price end as B_price
FROM dataTable 
GROUP BY sku

But its giving me error as ‘not a group by expression’.

Tags:

2

Answers


  1. 0

    You could sum over a case expression for each column:

    SELECT   sku,
         SUM(CASE grade WHEN 'A' THEN sku END) AS a_price,
         SUM(CASE grade WHEN 'B' THEN sku END) AS b_price
FROM     dataTable
GROUP BY sku
    Login or Signup to reply.
  2. 0

    You can do it by this query :

    select p.sku,  p.price as 'A_price', s.B_price
from products p
left join (
    select sku, price as 'B_price'
    from products
    where grade = 'B'
) as s on s.sku = p.sku
where p.grade = 'A';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search