CODEWITHSUNDEEP
mysqlsqljoinsumunion
nickruggiero
nickruggiero

Reputation: 161

Combining "SUMs" with a similar value

I have a feed updating my database with orders that are purchased/returned through the day. The table has a name, value, and order type. The following table is a view of the current DB (the order type is not on this diagram but is stored in the DB i.e. purchase/return)

Current DB Structure

My goal is to query this DB and return the following results:

Results

The purchase column would sum all of the purchases for the name, the return column would sum all of the returns for the name, and the net column would sum all values for the name.

Here is the following query I came up with: 

SELECT 
(SELECT NAME, SUM(ORDER)
FROM Orders
WHERE OrderType = "Purchase"
) AS PURCHASE,

(SELECT NAME, SUM(ORDER)
FROM Orders
WHERE OrderType = "Return"
) AS RETURN,

(SELECT NAME, SUM(ORDER)
FROM Orders
) AS NET

Here is the result:

enter image description here

The calculations are correct but I can not figure out how to combine the names and display the calculations in their own column. Any help is greatly appreciated, thank you!!

Upvotes: 2

Views: 36

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

You can use CASE WHEN:

SELECT NAME,
  SUM(CASE WHEN OrderType = 'Purchase' THEN `ORDER` ELSE 0 END) AS PURCHASE,
  SUM(CASE WHEN OrderType = 'Return' THEN `ORDER` ELSE 0 END) AS `RETURN`,       
  SUM(`Order`) AS Net
FROM Orders
GROUP BY NAME

Upvotes: 2

Related Questions