进行小组和SUM SQL

Question

我在分组另一列时试图总和一个列。SQL始终如一
给我两个单独的行。

以下是我的脚本和结果：

Select A.client, A.store, A.Type as 'Ticket Type', A.[bizdate] as 'DATE',     
sum(cast(round(c.[Amount2],2) as decimal (20,0))) 'QTY', c.SKU, c.[DESC], 
sum(cast(round(c.[Amount1],2)as decimal(20,2))) as 'Total'

From Mydatabase A inner join mydatabase.dbo.TicketHeader B on A.    
[ticket] = b.[ticket] and a.[yyyymmdd] = b.[yyyymmdd]
inner join mydatabase.dbo.ticketdetail C on C.[ticketheaderid]  =   
b.[id] and c.[class] = a.[class]
where  
A.Store = @store
and a.yyyymmdd = @yyyymmdd
and c.class <> 'balance'
and c.Subtype <> 'cancelled'
and a.type NOT IN    
('SKU','Total','CASH','CHANGE','NA','TAX1','TAX2','TAX3','TAX4','CHARGE',     
'Delivery Service', 'Tip')
and c.[DESC] <> 'NA'
and c.sku = '300129'
group by c.sku, c.[DESC],a.store, a.type, a.bizdate, a.client, c.[Amount2],    
c.[Amount1]  
order by [ticket type]
client, Store TicketType         date       qty   SKU    Description  Amount
1      2404  CURBSIDE / TO GO   20170214    1   300129  Beef TACO    2.00
1      2404  DINE IN            20170214    1   300129  Beef TACO    2.00
1      2404  DINE IN            20170214    2   300129  Beef TACO    4.00
1      2404  FOODSBY            20170214    2   300129  Beef TACO    4.00

我试图将牛肉炸玉米饼的用餐分组为一条线，而总结
数量。

Answer 1

您在这里有几个设计和代码问题。诸如MANES1和MANTE2之类的事情表明可能不良的标准化。别名A，B，C是一种坏习惯，因为它使维护方式比应有的难度更难。

• 踢的不良习惯：使用（a，b，c）或（t1，t2，t3）等表别名

从格式化的角度来看，您的代码旁边是一团糟，以至于与之合作。添加一些空白空间并将您的分组放置，这应该可以正常工作。

Select A.client
    , A.store
    , A.Type as 'Ticket Type'
    , A.[bizdate] as 'DATE'
    , sum(cast(round(c.[Amount2],2) as decimal (20,0))) 'QTY'
    , c.SKU
    , c.[DESC]
    , sum(cast(round(c.[Amount1],2)as decimal(20,2))) as 'Total'
From Mydatabase A 
inner join mydatabase.dbo.TicketHeader B on A.[ticket] = b.[ticket] 
                                        and a.[yyyymmdd] = b.[yyyymmdd]
inner join mydatabase.dbo.ticketdetail C on C.[ticketheaderid] = b.[id] and c.[class] = a.[class]
where  
    A.Store = @store
    and a.yyyymmdd = @yyyymmdd
    and c.class <> 'balance'
    and c.Subtype <> 'cancelled'
    and a.type NOT IN    
    (
        'SKU','Total','CASH','CHANGE','NA','TAX1','TAX2','TAX3','TAX4','CHARGE','Delivery Service', 'Tip'
    )
    and c.[DESC] <> 'NA'
    and c.sku = '300129'
group by A.client
    , A.store
    , A.Type 
    , A.[bizdate] 
    , c.SKU
    , c.[DESC]
order by [ticket type]