英文:
i cant make rollup in sql server to work as i want
问题
我正在使用SQL查询来生成以下结果。
在数据库的一个表中，我有大约十种产品，另一个表中有销售数据。
我的产品具有一些特殊属性。
- 有一个标志用于将产品分为SMALLV和BIGV两类。该标志只是true或false。
- 另一个数值变量确定每种产品中的酒精含量。它是小数，值为0.40或0.42。
- 还有一个数值变量，表示产品的容量。它可以采用不同值，如0.05、0.10、0.20、1或5升。
我构建了一个查询来获取所有这些产品的销售数据，但它们必须进行分类。我使用了大量的Sum函数，到目前为止效果很好。
问题出现在当我要求子总计时。这使我困惑，无法得到正确的结果。
我的查询目前是这样的：
Select it.itemdescription as item
,it.vatpercent as vat
,sum(it.AQty) as firstquant
,sum(it.bQty) as secquant
,itm.userdefnum02 as volume
,itm.userdefnum01 as [alc%]
,(itm.userdefnum02*itm.USERDEFNUM01*(sum(it.aQty))) as alclit
,(12.25*itm.userdefnum02*itm.USERDEFNUM01*(sum(it.aQty))) as tax
,itm.HEUSERDEFBOOL01 as flag
From centlines it
inner join items itm on itm.code=it.itemcode
inner join commercialentries ce on ce.dentid=it.dentid
group by itm.userdefnum01,it.vatpercent,itm.userdefbool01 ,it.itemdescription,itm.userdefnum02,ITM.code
现在，我想要有启用标志并具有相同“alc%”和相同“vat”的产品的子总计。这是否可行？
我的查询很长，所以我只是修改了它以向您展示我尝试实现的内容。
英文:
I am using an SQL query to produce the following results.
I have around ten products in a table in a database , and i also have sales in another table.
My products have some special attributes.
- there is a flag that is used to group the products to SMALLV and BIGV. The flag is just true or false
- another numeric variable that determines the alcohol in each product. It is decimal and has a value of 0.40 or 0.42
- another numeric variable that holds the volume of the product . It takes different values from 0.05 or 0.10 or 0.20 or 1 or 5 . These are Liters.
I built a query to get sales of all those products but they must be categorized. I use a lot of Sum and so far it works great.
The problem start when I ask for sub totals. It confuses me and I can't get the right results.
m
My query so far is something like,
Select it.itemdescription as item
,it.vatpercent as vat
,sum(it.AQty) as firstquant
,sum(it.bQty) as secquant
,itm.userdefnum02 as volume
,itm.userdefnum01 as [alc%]
,(itm.userdefnum02*itm.USERDEFNUM01*(sum(it.aQty))) as alclit
,(12.25*itm.userdefnum02*itm.USERDEFNUM01*(sum(it.aQty))) as tax
,itm.HEUSERDEFBOOL01 as flag
From centlines it
inner join items itm on itm.code=it.itemcode
inner join commercialentries ce on ce.dentid=it.dentid
group by itm.userdefnum01,it.vatpercent,itm.userdefbool01 ,it.itemdescription,itm.userdefnum02,ITM.code
Now I want subtotals of the products that have the flag enabled and have the same 'alc%' and same 'vat'.
Is this possible?
My query is big so I just changed it to show you what I try to acomplish.
答案1
得分: 0
看起来你需要使用 GROUPING SETS。
select
it.itemdescription as item
,it.vatpercent as vat
,sum(it.AQty) as firstquant
,sum(it.bQty) as secquant
,itm.userdefnum02 as volume
,itm.userdefnum01 as [alc%]
,itm.userdefnum02 * itm.USERDEFNUM01 * sum(it.aQty) as alclit
,itm.userdefnum02 * itm.USERDEFNUM01 * sum(it.aQty) * 12.25 as tax
,itm.HEUSERDEFBOOL01 as flag
from centlines it
inner join items itm on itm.code = it.itemcode
inner join commercialentries ce on ce.dentid = it.dentid
group by grouping sets (
(itm.HEUSERDEFBOOL01, it.vatpercent, it.itemdescription, itm.userdefnum01, itm.userdefnum02),
(itm.HEUSERDEFBOOL01, it.vatpercent, itm.userdefnum01)
);
英文:
Looks like you need GROUPING SETS
select
it.itemdescription as item
,it.vatpercent as vat
,sum(it.AQty) as firstquant
,sum(it.bQty) as secquant
,itm.userdefnum02 as volume
,itm.userdefnum01 as [alc%]
,itm.userdefnum02 * itm.USERDEFNUM01 * sum(it.aQty) as alclit
,itm.userdefnum02 * itm.USERDEFNUM01 * sum(it.aQty) * 12.25 as tax
,itm.HEUSERDEFBOOL01 as flag
from centlines it
inner join items itm on itm.code = it.itemcode
inner join commercialentries ce on ce.dentid = it.dentid
group by grouping sets (
(itm.HEUSERDEFBOOL01, it.vatpercent, it.itemdescription, itm.userdefnum01, itm.userdefnum02),
(itm.HEUSERDEFBOOL01, it.vatpercent, itm.userdefnum01)
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论