stats-ITShop.sql

--budget per team in 2019
SELECT intervention.STRUCTURE, SUM(intervention.Prix_total) AS "TOTAL in euros"
FROM intervention
WHERE (intervention.Date_maj > "2018-10-01 10:11:11") AND (intervention.STRUCTURE <> "-") AND (intervention.STRUCTURE IS NOT NULL)
GROUP BY intervention.STRUCTURE
ORDER BY SUM(intervention.Prix_total) DESC
LIMIT 0 , 500


--total items cost per team
SELECT intervention.STRUCTURE AS "Team",article.Article,SUM(intervention.Prix_total) AS "TOTAL in euros"
FROM intervention
INNER JOIN article ON intervention.Code_article = article.Code_article
WHERE (intervention.Date_maj > "2018-10-01 10:11:11") AND (intervention.STRUCTURE <> "-") AND (intervention.STRUCTURE IS NOT NULL)
GROUP BY article.Article, intervention.STRUCTURE
ORDER BY article.Article, SUM(intervention.Prix_total) DESC
LIMIT 0 , 500
INTO OUTFILE '/tmp/commandMagasin2019.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'


--total items cost
SELECT article.Article,SUM(intervention.Prix_total) AS "TOTAL in euros"
FROM intervention
INNER JOIN article ON intervention.Code_article = article.Code_article
WHERE (intervention.Date_maj > "2018-10-01 10:11:11") AND (intervention.Prix_total IS NOT NULL)
GROUP BY article.Article
ORDER BY SUM(intervention.Prix_total) DESC
LIMIT 0 , 500