SQL Oracle查询计算小计 [英] SQL Oracle query to calculate subtotal

问题描述

我正在谷歌搜索以适当的方法在Oracle SQL中进行小计.对此进行记录查询

I am googling to find proper way to make subtotals in Oracle SQL. Recording to this i make query

select model,  sifra, velicina, sum(nvl(magacin,0)) as suma
from podmornica
where model ='30001'
group by  sifra, velicina, cube (model)
order by model, sifra, velicina

我的桌子上有列podmornica，其列为:model，sifra，velicina，magacin

I have table podmornica with columns:model, sifra, velicina, magacin

但是它不起作用.列模型中的第二行均为空，最后不计算总和.怎么解决呢? 谢谢

But it doesn't work. Every second row in column model is null, and at the end not calculate sum. How to solve this? Thanks

P.S.在一个模型中，我们有SIFRA的变体，因此，对于一个模型(在这种情况下，模型为30001)，我将不具有每个SIFRA的小计.像下面一样

P.S. In one MODEL we have variations of SIFRA, i wan't as result to have subtotals for each SIFRA for one model (in this case model is 30001). Like below

MODEL  SIFRA     VELICINA  SUMA

30001  3000101      0        1
30001  3000102      0        2
30001  3000103      0        5
______________________________
30001                        8

推荐答案

这似乎是使用group by grouping sets ...

This appears to be a good time to use group by grouping sets...

SELECT MODEL,  SIFRA,     VELICINA,  sum(nvl(magacin,0)) as SUMA
FROM podmornica
WHERE model ='30001'
GROUP BY GROUPING SETS ((MODEL, SIFRA, VELICINA), (Model))

按模型，sifra和velicina分组以获取详细信息行.并用这3个字段加起来的magacin ...

Group by the model, sifra and velicina to get the detail rows. with magacin summed by those 3 fields...

按模型分组，以便显示给定模型的总数.

Group by model so that the sum total is shown for a given model.

或者，如果您想添加一列，则可以通过在选择中添加sum(magacin) over (partition by model) as sumB来显示模型每一行的总数.这种方法使用了分析/窗口功能.

Alternatively if you wanted to add a column, you could show the total on every line for the model by adding a sum(magacin) over (partition by model) as sumB to the select. This approach is using an analytic/window function.

这篇关于SQL Oracle查询计算小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！