CODEWITHSUNDEEP
mysqlsql
user4943236
user4943236

Reputation: 6324

How to subtract the one row from another MySQL

Given that a table as enclosed

enter image description here

How do I find the difference between Mac and Windows sold each day. Can someone pls explain the logic

Upvotes: 0

Views: 154

Answers (2)

1000111
1000111

Reputation: 13519

An INNER JOIN would do the job.

SELECT 
WindowsTable.Date,
ABS(WindowsTable.Sold - MacTable.Sold) absoluteDifference 
FROM
(SELECT 
*
FROM producttable
WHERE Products = 'Windows') WindowsTable

INNER JOIN 

(
SELECT 
*
FROM producttable
WHERE Products = 'Mac' ) MacTable

ON WindowsTable.Date = MacTable.Date;

DEMO HERE

Upvotes: 2

Anthony E
Anthony E

Reputation: 11235

Try reshaping your query using an INNER JOIN on date:

SELECT macs_sales.Date, (MacsSold - WindowsSold) AS sales_difference
FROM
(
  SELECT Date, Sold as MacsSold
  FROM computer_sales
  WHERE Products="Mac"
) macs_sales
INNER JOIN
(
  SELECT Date, Sold as WindowsSold
  FROM computer_sales
  WHERE Products="Windows"
) windows_sales
ON macs_sales.Date = windows_sales.Date

Upvotes: 1

Related Questions