在我的SQL select语句中汇总并添加Summary Rows [英] Summarise and add Summary Rows in my SQL select statement

问题描述

我的查询是用于汇总表格中的总数，但我需要一些帮助来自定义查询以生成我正在寻找的结果。到目前为止，这是我所拥有的查询，但它缺少2行结算股票和差异
这些是计算行。我如何提出结算股票和差异 

< pre class ="prettyprint"> SELECT TxDate，TxStage，ItemCode，ItemName，SupplierWeight，CustomerOrderedWeight，DecanterWeight，
SupplierWeight + CustomerOrderedWeight + DecanterWeight AS Quantity，
CASE WHEN TxStage ='BITUTAINER IN'THEN'Inbound'
WHEN TxStage ='BITUTAINER OUT'那么'出境'
当TxStage ='STOCK TAKE ON'那么'股票买入'
当TxStage ='BOX接受'那么'实物股票'
WHEN TxStage ='DECANTER IN'那么'Decanter'
END作为TypeOfTransaction，
情况当TxStage ='BITUTAINER IN'那么'3'
当TxStage ='BITUTAINER OUT'时那么'5'
当TxStage ='STOCK TAKE ON'那么'1'
当TxStage ='BOX TAKE ON'那么'6'
当TxStage ='DECANTER''那么'4 '
END AS DisplayOrder
来自dbo.Transactions_Transactions _Main

上图显示了我在上面运行查询时得到的结果

---

如果你认为你可以实现它

解决方案

看看这是否适合你

WITH cte

AS

（

SELECT TxDate，TxStage，ItemCode，ItemName，SupplierWeight，CustomerOrderedWeight，DecanterWeight，

SupplierWeight + CustomerOrderedWeight + DecanterWeight AS数量，

情况当TxStage ='BITUTAINER IN'那么'入境' 

当TxStage ='BITUTAINER OUT'那么'Outbound'


当TxStage ='STOCK TAKE ON'那么'股票上涨'¥b $ b当TxStage ='BOX TAKE ON'那么'实体股票'为
WHEN  TxStage ='DECANTER IN'那么'Decanter'

结束作为TypeOfTransaction，

案例当TxStage ='BITUTAINER''那么'3' 

当TxStage ='BITUTAINER OUT'然后'5'

当TxStage ='STOCK TAKE ON'那么'1'

当TxStage ='BOX TAKE ON'那么'6'时
WHEN  TxStage ='DECANTER IN'THEN'4'
$
END作为DisplayOrder

来自dbo.Transactions_Transactions_Main 

） 

SELECT *，variance = closing_stocks +' - '+ TypeOfTransaction ='Outbound'时的情况 然后TypeOfTransaction 否则''结束 

FROM 

（

SELECT *，closing_stocks =当TypeOfTransaction ='Stock Take On'时的情况'然后TypeOfTransaction + '+'其他''

 结束+案例，当TypeOfTransaction ='入境' 然后TypeOfTransaction +'+'else''结束+

 案例何时TypeOfTransaction ='Decanter' 然后TypeOfTransaction +'+'else''结束 

  + CaseOfTransaction ='Outbound'时的情况 然后TypeOfTransaction +'+'else''结束，

  FROM cte

）AS

I have my query that I am using to summarize the totals in my table but I need some help customizing the query to produce the results that I am looking for. So far this is the query That I have , but it is missing 2 Rows the closing stock and the Variance which are calculated rows. How do I come up with the Closing Stock and the Variance 

SELECT TxDate, TxStage, ItemCode, ItemName, SupplierWeight, CustomerOrderedWeight,DecanterWeight,
SupplierWeight+CustomerOrderedWeight+DecanterWeight AS Quantity,
CASE WHEN TxStage='BITUTAINER IN' THEN 'Inbound' 
WHEN TxStage='BITUTAINER OUT' THEN 'Outbound'	
WHEN TxStage='STOCK TAKE ON' THEN 'Stock Take On'
WHEN TxStage='BOX TAKE ON' THEN 'Physical Stock'
WHEN  TxStage='DECANTER IN' THEN 'Decanter'
END AS TypeOfTransaction,
CASE WHEN TxStage='BITUTAINER IN' THEN '3' 
WHEN TxStage='BITUTAINER OUT' THEN '5'	
WHEN TxStage='STOCK TAKE ON' THEN '1'
WHEN TxStage='BOX TAKE ON' THEN '6'
WHEN  TxStage='DECANTER IN' THEN '4'
END AS DisplayOrder
FROM dbo.Transactions_Transactions_Main 
		

		

The image above shows the result I get when I run the query above

---

If you think it you can achieve it

解决方案

See if this works for you

WITH cte
AS
(
SELECT TxDate, TxStage, ItemCode, ItemName, SupplierWeight, CustomerOrderedWeight,DecanterWeight,
SupplierWeight+CustomerOrderedWeight+DecanterWeight AS Quantity,
CASE WHEN TxStage='BITUTAINER IN' THEN 'Inbound' 
WHEN TxStage='BITUTAINER OUT' THEN 'Outbound'
WHEN TxStage='STOCK TAKE ON' THEN 'Stock Take On'
WHEN TxStage='BOX TAKE ON' THEN 'Physical Stock'
WHEN  TxStage='DECANTER IN' THEN 'Decanter'
END AS TypeOfTransaction,
CASE WHEN TxStage='BITUTAINER IN' THEN '3' 
WHEN TxStage='BITUTAINER OUT' THEN '5'
WHEN TxStage='STOCK TAKE ON' THEN '1'
WHEN TxStage='BOX TAKE ON' THEN '6'
WHEN  TxStage='DECANTER IN' THEN '4'
END AS DisplayOrder
FROM dbo.Transactions_Transactions_Main 
) 
SELECT * ,variance =closing_stocks+ '-'+case when TypeOfTransaction ='Outbound'  then TypeOfTransaction  else '' end 
FROM 
(
SELECT * , closing_stocks =case when TypeOfTransaction ='Stock Take On'  then TypeOfTransaction+'+' else ''
 end + case when TypeOfTransaction ='Inbound'  then TypeOfTransaction +'+' else '' end +
 case when TypeOfTransaction ='Decanter'  then TypeOfTransaction +'+' else '' end 
 +case when TypeOfTransaction ='Outbound'  then TypeOfTransaction +'+' else '' end ,
  FROM cte
) AS der

这篇关于在我的SQL select语句中汇总并添加Summary Rows的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！