0

This is my query:

SELECT llx_societe.nom as "Customer",
sum(llx_facturedet.total_ht) AS "2020"
FROM llx_facture AS t
LEFT JOIN llx_societe ON t.fk_soc = llx_societe.rowid
LEFT JOIN llx_facturedet ON t.rowid = llx_facturedet.fk_facture
LEFT JOIN llx_product ON llx_facturedet.fk_product = llx_product.rowid
WHERE date_format(t.datef,'%Y') = '2020' AND t.entity IN (1,3)
GROUP BY llx_societe.nom
ORDER BY 2 DESC;

Just like the results of 2020, i want to add 2 more columns named 2019 and 2018. I thought of union but it did not work. I am stuck here kindly guide me

Thom A
  • 88,727
  • 11
  • 45
  • 75
Talha Jaleel Chatha
  • 1
  • 1
  • 3

1 Answers1

1

Use conditional aggregation:

SELECT llx_societe.nom as "Customer",
       SUM(CASE WHEN year(t.datef) = 2020 THEN llx_facturedet.total_ht ELSE 0 END) AS "2020",
       SUM(CASE WHEN year(t.datef) = 2019 THEN llx_facturedet.total_ht ELSE 0 END) AS "2019",
       SUM(CASE WHEN year(t.datef) = 2018 THEN llx_facturedet.total_ht ELSE 0 END) AS "2018",
FROM llx_facture t LEFT JOIN
     llx_societe
     ON t.fk_soc = llx_societe.rowid LEFT JOIN
     llx_facturedet
     ON t.rowid = llx_facturedet.fk_facture LEFT JOIN
     llx_product
     ON llx_facturedet.fk_product = llx_product.rowid
WHERE t.entity IN (1, 3)
GROUP BY llx_societe.nom
ORDER BY 2 DESC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • If your table contains also records of other years it may help to filter by adding a where condition: `t.datef BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2020-12-31' AS DATE)`. Otherwise you may have (old) customers in your result with all zero sums. – Conffusion Oct 13 '20 at 15:24
  • thank you, my issue has been resolved:) – Talha Jaleel Chatha Oct 13 '20 at 15:31