SQL Compare two periods in a table and obtain sales results

user_10112622 0
Viewed 675

This table is a simple example of my real table with exporters, products, marks, pallets, quantities, etc.

But I think this table is most easy to know my trouble.

+----+--------+----------+----------+----------+------------+
| Id | Seller | Products | Marks    | Quantity | Date       |
+----+--------+----------+----------+----------+------------+
| 01 | John   | Watch    | Apple    |        5 | 2017-01-01 |
| 02 | Peter  | Mouse    | Apple    |       21 | 2017-01-01 |
| 02 | Peter  | Mouse    | Logitech |       10 | 2017-01-02 |
| 01 | John   | Mouse    | Logitech |       15 | 2017-01-03 |
| 02 | Peter  | Keyboard | Logitech |       11 | 2017-01-05 |
| 01 | John   | Keyboard | Apple    |       10 | 2017-01-06 |
| 02 | Peter  | Keyboard | Apple    |       14 | 2017-01-06 |
| 01 | John   | Keyboard | Logitech |       12 | 2017-01-06 |
| 01 | John   | Keyboard | Apple    |       17 | 2017-01-07 |
| 01 | John   | Mouse    | Logitech |       13 | 2017-01-08 |
| 01 | John   | Watch    | Apple    |        6 | 2017-01-09 |
| .  |  .     |   .      |    .     |       .  |      .     | 
| 01 | Peter  | Mouse    | Logitech |        5 | 2018-01-01 |
| 02 | Steve  | Keyboard | Apple    |        7 | 2018-01-03 |
| 02 | Peter  | Watch    | Apple    |        8 | 2018-01-03 |
| 01 | John   | Mouse    | Logitech |        6 | 2018-01-04 |
| 02 | Peter  | Watch    | Samsung  |        9 | 2018-01-04 |
| 01 | John   | Keyboard | Apple    |       18 | 2018-01-08 |
| 01 | John   | Watch    | Apple    |        7 | 2018-01-08 |
| 02 | Peter  | Phone    | Xiaomi   |       19 | 2018-01-08 |
| 01 | John   | Keyboard | Logitech |       15 | 2018-01-06 |
| 01 | Peter  | TV       | Samsung  |       20 | 2018-01-07 |
| 01 | Steve  | Display  | LG       |       11 | 2018-01-08 |
| 01 | John   | Display  | Samsung  |       14 | 2018-01-08 |
| .  |  .     |   .      |    .     |       .  |      .     | 
+----+--------+----------+----------+----------+------------+

I need to compare two periods (For example January 2018 vs January 2017) in a same table for distint Sellers to knows sales results.

Some like this...

+----+--------+----------+----------+------------+------------+-----------+
| Id | Seller | Products | Marks    | Total 2017 | Total 2018 | Diference |
+----+--------+----------+----------+------------+------------+-----------+
| 01 | John   | Watch    | Apple    |         11 |          7 |        -4 |
| 01 | John   | Mouse    | Logitech |         28 |          6 |       -22 |
| 01 | John   | Keyboard | Apple    |         17 |         18 |         1 |
| 01 | John   | Keyboard | Logitech |         12 |         15 |         3 |
| 01 | John   | Display  | Samsung  |          0 |         14 |        14 |
| 02 | Peter  | Mouse    | Apple    |         21 |          0 |       -21 |
| 02 | Peter  | Mouse    | Logitech |         10 |          5 |        -5 |
| 02 | Peter  | Keyboard | Logitech |         11 |          0 |       -11 |
| .  |  .     |   .      |    .     |     .      |      .     |     .     |
| .  |  .     |   .      |    .     |     .      |      .     |     .     |
| .  |  .     |   .      |    .     |     .      |      .     |     .     |
+----+--------+----------+----------+------------+------------+-----------+

I have tried with these Queries, but I get repeated values and Sellers are not showed properly.

SELECT *, (Q1 - Q2) AS Diference

FROM

(SELECT Id, Seller, Products, Marks, SUM(Quantity) AS Q1
WHERE Date >= '2018-01-01' AND Date <= '2018-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS A

INNER JOIN

(SELECT Id AS Id2, Seller, Products, Marks, SUM(Quantity) AS Q2
WHERE Date >= '2017-01-01' AND Date <= '2017-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS B

ON A.Id = B.Id2

SELECT *, (Q1 - Q2) AS Diference,
       (CASE 
           WHEN Id IS NULL THEN Id2
          ELSE Id
       END) AS Id3,
       (CASE 
           WHEN Id IS NULL THEN Seller2
          ELSE Seller
       END) AS Seller3

FROM

(SELECT Id, Seller, Products, Marks, SUM(Quantity) AS Q1
WHERE Date >= '2018-01-01' AND Date <= '2018-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS A

INNER JOIN

(SELECT Id AS Id2, Seller AS Seller2, Products AS Products2, Marks, SUM(Quantity) AS Q2
WHERE Date >= '2017-01-01' AND Date <= '2017-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS B

ON A.Id = B.Id2

Some help will be apreciated.

Thanks in advance.

mariadb pivot-table mysql sql java
2 Answers

Just use conditional aggregation:

select Id, Seller, Products, Marks,
       sum(case when year(date) = 2017 then quantity end) as quantity_2017,
       sum(case when year(date) = 2018 then quantity end) as quantity_2018,
       (sum(case when year(date) = 2018 then quantity end) -
        sum(case when year(date) = 2017 then quantity end) 
       ) as diff
from realtable
group by Id, Seller, Products, Marks;
user_11440350

Finally, It Works Great!

Thanks Gordon Linoff!

SELECT ex.id_exportador,
       exp.exportador,
       ex.id_producto, pd.producto,
       SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.bultos ELSE 0 END)) AS b1,
       SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.bultos ELSE 0 END)) AS b2,
       SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.kg_brutos ELSE 0 END)) AS kgb1,
       SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.kg_brutos ELSE 0 END)) AS kgb2,
       SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.kg_netos ELSE 0 END)) AS kgn1,
       SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.kg_netos ELSE 0 END)) AS kgn2,
       SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.palets ELSE 0 END)) AS pal1,
       SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.palets ELSE 0 END)) AS pal2,
       SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.europalets ELSE 0 END)) AS epal1,
       SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.europalets ELSE 0 END)) AS epal2

FROM servicios s

LEFT JOIN expedientes ex
     ON s.codservicio = ex.codservicio

LEFT JOIN exportadores exp
     ON ex.id_exportador = exp.id_exportador

LEFT JOIN vales val
     ON ex.id_expediente = val.id_expediente

LEFT JOIN productos pd
     ON ex.id_producto = pd.id_producto

WHERE ((s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) OR (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal})) AND ex.id_exportador IS NOT NULL

GROUP BY ex.id_exportador, ex.id_producto
user_101126220
Related