Vue sql server 2008

Question

Bonjour à tous,

J'ai une table des ventes avec comme colonne :

Pays, Produit, Année, Mois, Qté

Je voudrais faire une vue pour avoir comme colonne :

Pays, Produit, Total Qté 2010, Total Qté 2009, écart 2010-2009, écart en %, moyenne de qté.

J'arrive a faire une vue avec les total pour 2010 mais j'arrive pas à intégrer aussi le 2009.

Voici ma vue pour intégrer Total Qté 2010 :

SELECT     dbo.country.country, dbo.product.code, SUM(dbo.sales.quantity) AS [QTY 2010]
FROM         dbo.country 
INNER JOIN
                      dbo.sales ON dbo.country.id = dbo.sales.country_id 
INNER JOIN
                      dbo.product ON dbo.sales.product_id = dbo.product.id
GROUP BY dbo.country.country, dbo.product.code, dbo.sales.year
ORDER BY dbo.country.country, dbo.product.code, MAX(dbo.sales.month)

Merci

cs_Robert33 · Answer

Bonjour
essaie ça ...
mais je ne garantis pas les performances



SELECT  Country.Country, 
  product.Code, 
  SUM(sales_1.quantity) AS [QTY 2009], 
  SUM(sales.quantity) AS [QTY 2010], 
  SUM(sales.quantity - sales_1.quantity) AS [Ecart 2009-2010], 
  AVG(sales_1.quantity) AS [moy 2009], 
  AVG(sales.quantity) AS [moy 2010], 
  cast (100* CAST ( SUM(sales_1.quantity) AS decimal(10,2)) / (select sum(sales.quantity) from sales where (sales.[year] = 2010)) AS decimal(10,2))   AS [pourcent 2009],
  cast (100* CAST ( SUM(sales.quantity) AS decimal(10,2)) / (select sum(sales.quantity) from sales where (sales.[year] = 2009)) AS decimal(10,2))   AS [pourcent 2010]
FROM Country 
 INNER JOIN sales ON Country.id = sales.country_id 
 INNER JOIN product ON sales.product_id = product.id 
 INNER JOIN sales sales_1 ON product.id sales_1.product_id AND Country.id sales_1.country_id

WHERE (sales.[year] = 2010) AND (sales_1.[year] = 2009)
GROUP BY Country.Country, product.Code, product.id
ORDER BY Country.Country, product.Code


Bob.
C# is amazing, enjoy it!

cs_Robert33 · Answer

ouups, une petite erreur dans un Where



SELECT  Country.Country, 
  product.Code, 
  SUM(sales_1.quantity) AS [QTY 2009], 
  SUM(sales.quantity) AS [QTY 2010], 
  SUM(sales.quantity - sales_1.quantity) AS [Ecart 2009-2010], 
  AVG(sales_1.quantity) AS [moy 2009], 
  AVG(sales.quantity) AS [moy 2010], 
  cast (100* CAST ( SUM(sales_1.quantity) AS decimal(10,2)) / (select sum(sales.quantity) from sales where (sales.[year] = 2009)) AS decimal(10,2))   AS [pourcent 2009],
  cast (100* CAST ( SUM(sales.quantity) AS decimal(10,2)) / (select sum(sales.quantity) from sales where (sales.[year] = 2010)) AS decimal(10,2))   AS [pourcent 2010]
FROM Country 
 INNER JOIN sales ON Country.id = sales.country_id 
 INNER JOIN product ON sales.product_id = product.id 
 INNER JOIN sales sales_1 ON product.id sales_1.product_id AND Country.id sales_1.country_id

WHERE (sales.[year] = 2010) AND (sales_1.[year] = 2009)
GROUP BY Country.Country, product.Code, product.id
ORDER BY Country.Country, product.Code


C# is amazing, enjoy it!

Vue sql server 2008

2 réponses

Votre réponse

Discussions similaires