SQL Group By Sum和Nulls(SQL Group By Sum and Nulls)

SQL Group By Sum和Nulls(SQL Group By Sum and Nulls)

我有一个查询，用GROUP BY获取某个日期的数量总和，但查询给我NULL结果，而不是聚合一些值。

这是查询：

Select VPC.Armazem as TARMA ,YEAR(VPC.data) as DataTotal ,CASE WHEN VP.combustivel = 1 THEN ISNULL(SUM(VPL.QTD), 0) END as ADITIVADA ,CASE WHEN VP.combustivel = 2 THEN ISNULL(SUM(VPL.QTD), 0) END as X98 ,CASE WHEN VP.combustivel = 3 THEN ISNULL(SUM(VPL.QTD), 0) END as X95 ,CASE WHEN VP.combustivel = 4 THEN ISNULL(SUM(VPL.QTD), 0) END as XGAS ,CASE WHEN VP.combustivel = 5 THEN ISNULL(SUM(VPL.QTD), 0) END as XGPL ,CASE WHEN VP.combustivel = 6 THEN ISNULL(SUM(VPL.QTD), 0) END as XAGR ,CASE WHEN VP.combustivel = 7 THEN ISNULL(SUM(VPL.QTD), 0) END as MISTURA ,CASE WHEN VP.combustivel = 9 THEN ISNULL(SUM(VPL.QTD), 0) END as XAQ ,CASE WHEN VP.combustivel = 10 THEN ISNULL(SUM(VPL.QTD), 0) END as ADIESEL ,CASE WHEN VP.combustivel = 11 THEN ISNULL(SUM(VPL.QTD), 0) END as ADBLUE ,CASE WHEN VP.combustivel = 12 THEN ISNULL(SUM(VPL.QTD), 0) END as O95 ,CASE WHEN VP.combustivel = 13 THEN ISNULL(SUM(VPL.QTD), 0) END as O98 FROM CB_VendasPOS (nolock) as VP INNER JOIN vendaspos_linhas (nolock) as VPL on VPL.autoreg=VP.autoreg INNER JOIN VendasPOS_Cabecalhos (nolock) as VPC on VPC.Prenumero=VPL.Prenumero INNER JOIN VendasPOS_Turnos (nolock) as VPT on VPT.CodTurnoAuto=VPC.CodTurnoAuto WHERE (MONTH(VPC.data) >= MONTH('2015-09-01') AND MONTH(VPC.data) <= MONTH('2015-09-01')) and (YEAR(VPC.data) >= YEAR('2014-09-01') AND YEAR(VPC.data) <= YEAR('2015-09-01')) and (VPT.armazem='454' or Len('454')=0) and FACT_VD NOT IN ('A', 'I', 'G', 'M') GROUP BY YEAR(VPC.data) ,VPC.Armazem ,VP.combustivel ORDER BY VPC.Armazem ,YEAR(VPC.data)

这是结果：

如何将结果汇总到每年只显示？

我正在使用MS SQL

I have a query that fetch the sum of quantity of a certain date with a GROUP BY, but the query is giving me NULL results and not aggregating some values.

Here is the query:

Select VPC.Armazem as TARMA ,YEAR(VPC.data) as DataTotal ,CASE WHEN VP.combustivel = 1 THEN ISNULL(SUM(VPL.QTD), 0) END as ADITIVADA ,CASE WHEN VP.combustivel = 2 THEN ISNULL(SUM(VPL.QTD), 0) END as X98 ,CASE WHEN VP.combustivel = 3 THEN ISNULL(SUM(VPL.QTD), 0) END as X95 ,CASE WHEN VP.combustivel = 4 THEN ISNULL(SUM(VPL.QTD), 0) END as XGAS ,CASE WHEN VP.combustivel = 5 THEN ISNULL(SUM(VPL.QTD), 0) END as XGPL ,CASE WHEN VP.combustivel = 6 THEN ISNULL(SUM(VPL.QTD), 0) END as XAGR ,CASE WHEN VP.combustivel = 7 THEN ISNULL(SUM(VPL.QTD), 0) END as MISTURA ,CASE WHEN VP.combustivel = 9 THEN ISNULL(SUM(VPL.QTD), 0) END as XAQ ,CASE WHEN VP.combustivel = 10 THEN ISNULL(SUM(VPL.QTD), 0) END as ADIESEL ,CASE WHEN VP.combustivel = 11 THEN ISNULL(SUM(VPL.QTD), 0) END as ADBLUE ,CASE WHEN VP.combustivel = 12 THEN ISNULL(SUM(VPL.QTD), 0) END as O95 ,CASE WHEN VP.combustivel = 13 THEN ISNULL(SUM(VPL.QTD), 0) END as O98 FROM CB_VendasPOS (nolock) as VP INNER JOIN vendaspos_linhas (nolock) as VPL on VPL.autoreg=VP.autoreg INNER JOIN VendasPOS_Cabecalhos (nolock) as VPC on VPC.Prenumero=VPL.Prenumero INNER JOIN VendasPOS_Turnos (nolock) as VPT on VPT.CodTurnoAuto=VPC.CodTurnoAuto WHERE (MONTH(VPC.data) >= MONTH('2015-09-01') AND MONTH(VPC.data) <= MONTH('2015-09-01')) and (YEAR(VPC.data) >= YEAR('2014-09-01') AND YEAR(VPC.data) <= YEAR('2015-09-01')) and (VPT.armazem='454' or Len('454')=0) and FACT_VD NOT IN ('A', 'I', 'G', 'M') GROUP BY YEAR(VPC.data) ,VPC.Armazem ,VP.combustivel ORDER BY VPC.Armazem ,YEAR(VPC.data)

And here is the result:

How can I aggregate the result to only show per year?

I'm using MS SQL

最满意答案

我怀疑你想要条件聚合：

Select VPC.Armazem as TARMA, YEAR(VPC.data) as DataTotal, SUM(CASE WHEN VP.combustivel = 1 THEN VPL.QTD ELSE 0 END) as ADITIVADA, SUM(CASE WHEN VP.combustivel = 2 THEN VPL.QTD ELSE 0 END) as X98, . . . FROM . . . GROUP BY YEAR(VPC.data), VPC.Armazem . . .

请注意， VP.combustivel不在GROUP BY 。

I suspect you want conditional aggregation:

Select VPC.Armazem as TARMA, YEAR(VPC.data) as DataTotal, SUM(CASE WHEN VP.combustivel = 1 THEN VPL.QTD ELSE 0 END) as ADITIVADA, SUM(CASE WHEN VP.combustivel = 2 THEN VPL.QTD ELSE 0 END) as X98, . . . FROM . . . GROUP BY YEAR(VPC.data), VPC.Armazem . . .

Note that VP.combustivel is not in the GROUP BY.