J'ai besoin d'une comparaison des ventes entre deux périodes de rapport. Il devrait afficher des ventes qui ont seulement diminué par rapport à l'année précédente. J'ai essayé la requête ci-dessous pour obtenir des valeurs.Mais comment puis-je ajouter une fonction d'agrégation dans la clause where.
SELECT CardCode, CardName, Sum(case when Year(DocDate)='2018' then DocTotal else 0 end) as Sold2018, Sum(case when Year(DocDate)='2019' then DocTotal else 0 end) as Sold2019 FROM ORDR ***where Sold2018 < Sold2019*** Group By CardCode, CardName
3 Réponses :
Utilisez la clause ayant
:
select * from ( SELECT CardCode, CardName, Sum(case when Year(DocDate)='2018' then DocTotal else 0 end) as Sold2018, Sum(case when Year(DocDate)='2019' then DocTotal else 0 end) as Sold2019 FROM ORDR Group By CardCode, CardName ) where Sold2018 < Sold2019
ou utilisez la sous-requête et la clause where
:
SELECT CardCode, CardName, Sum(case when Year(DocDate)='2018' then DocTotal else 0 end) as Sold2018, Sum(case when Year(DocDate)='2019' then DocTotal else 0 end) as Sold2019 FROM ORDR Group By CardCode, CardName having Sum(case when Year(DocDate)='2018' then DocTotal else 0 end) < Sum(case when Year(DocDate)='2019' then DocTotal else 0 end)
Utilisez simplement HAVING
, comme ci-dessous:
SELECT CardCode, CardName, Sum(case when Year(DocDate)='2018' then DocTotal else 0 end) as Sold2018, Sum(case when Year(DocDate)='2019' then DocTotal else 0 end) as Sold2019 FROM ORDR Group By CardCode, CardName having(Sum(case when Year(DocDate)='2018' then DocTotal else 0 end) < Sum(case when Year(DocDate)='2019' then DocTotal else 0 end))
Vous pouvez utiliser comme réponse with totalOf2018 as (
select CardCode, CardName, Sum(DocTotal) as Sold2018,
from ORDR
where Year(DocDate)='2018'
Group By CardCode, CardName
), totalOf2019 as(
select CardCode, CardName, Sum(DocTotal) as Sold2019,
from ORDR
where Year(DocDate)='2019'
Group By CardCode, CardName
)
select
a.CardCode, a.CardName, a.Sold2018, b.Sold2019
from totalOf2018 a
join totalOf2019 b on a.CardCode = b.CardCode and a.CardName = b.CardName
where a.Sold2018 < b.Sold2019
Clause
HAVING