0

As an example, I've a table like this: 

CREATE TABLE Sample (`id` int, `productName` varchar(7), `description` varchar(55), `quantity`int(10)) ; INSERT INTO Sample (`id`, `productName`, `description`, `quantity`) VALUES (1, 'Cheese', 'GT', 10), (1, 'Cheese', 'GE', 10), (1, 'Cheese', 'GE', 10), (2, 'Ham', 'VD', 10), (3, 'Wine', 'EX', 5) ;

and I'm trying to get a result so that the calculation is different if the description is of value GT and GE or VD. I tried with union all, but it does not seem to work: 

SELECT x.productName, SUM(x.quantity) FROM ( SELECT productName, SUM(quantity*10) FROM Sample WHERE description IN ('GT', 'GE') UNION ALL SELECT productName, SUM(quantity*20) FROM Sample WHERE description IN ('VD') ) AS x

Is this possible?

Improve this question

2 Answers 2

Reset to default
2
  • Don't forget GROUP BY clause when using aggregate functions.
  • Your inner SUMs aren't aliased, so x.quantity won't work.

Try this :

SELECT x.productName, SUM(x.quantity) as quantity FROM ( SELECT productName, SUM(quantity*10) as quantity FROM Sample WHERE description IN ('GT','GE') GROUP BY productName UNION ALL SELECT productName, SUM(quantity*20) as quantity FROM Sample WHERE description IN ('VD') GROUP BY productName ) AS x GROUP BY x.productName
Improve this answer
0
0 
with GT_GE as ( select productname, sum(quantity*10) Quantity from sample where description IN ('GT','GE') group by productname ) , VD as ( select productname, sum(quantity*20) Quantity from sample where description = 'VD' group by productname ) select GT_GE.productname, GT_GE.Quantity, VD.productname, VD.quantity from GT_GE, VD
Improve this answer
1
  • CTEs ("with") are available only in MariaDB 10.2 and MySQL 8.0.1. Commented Apr 29, 2017 at 6:03

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.