Welcome to Subscribe On Youtube
1445. Apples & Oranges
Description
Table: Sales
+---------------+---------+ | Column Name | Type | +---------------+---------+ | sale_date | date | | fruit | enum | | sold_num | int | +---------------+---------+ (sale_date, fruit) is the primary key (combination of columns with unique values) of this table. This table contains the sales of "apples" and "oranges" sold each day.
Write a solution to report the difference between the number of apples and oranges sold each day.
Return the result table ordered by sale_date.
The result format is in the following example.
Example 1:
Input: Sales table: +------------+------------+-------------+ | sale_date | fruit | sold_num | +------------+------------+-------------+ | 2020-05-01 | apples | 10 | | 2020-05-01 | oranges | 8 | | 2020-05-02 | apples | 15 | | 2020-05-02 | oranges | 15 | | 2020-05-03 | apples | 20 | | 2020-05-03 | oranges | 0 | | 2020-05-04 | apples | 15 | | 2020-05-04 | oranges | 16 | +------------+------------+-------------+ Output: +------------+--------------+ | sale_date | diff | +------------+--------------+ | 2020-05-01 | 2 | | 2020-05-02 | 0 | | 2020-05-03 | 20 | | 2020-05-04 | -1 | +------------+--------------+ Explanation: Day 2020-05-01, 10 apples and 8 oranges were sold (Difference 10 - 8 = 2). Day 2020-05-02, 15 apples and 15 oranges were sold (Difference 15 - 15 = 0). Day 2020-05-03, 20 apples and 0 oranges were sold (Difference 20 - 0 = 20). Day 2020-05-04, 15 apples and 16 oranges were sold (Difference 15 - 16 = -1).
Solutions
Solution 1: Group By + Sum
We can group the data by date, and then use the sum function to calculate the difference in sales between apples and oranges for each day. If it is an apple, we represent it with a positive number, and if it is an orange, we represent it with a negative number. Finally, we sort the data by date.
-
# Write your MySQL query statement below SELECT sale_date, SUM(IF(fruit = 'apples', sold_num, -sold_num)) AS diff FROM Sales GROUP BY 1 ORDER BY 1;