Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

sql server - SQL query: how to create subtotal rows when there is no aggregate function

Is there a function in SQL that can display subtotal rows. I have a table like this:

Date    INVNUNBER     CUSTOMER     ITEM     QTY     SALES
20190630 IN3343       joe's comp   23225    2.0     3000
20190630 IN3343       joe's comp   23214    1.0     400
20190630 IN3353       matt's comp. 12222     3.0     6000
20190630 IN3353       matt's comp. 32222     3.0     3000 

I tried ROLLUP, but seems like ROLLUP requires an aggregate function where I have to SUM up one of the fields, and all other fields need to be in the Group By clause, but I don't really need anything grouped:

I tried:

SELECT DATE, INVNUMBER, CUSTOMER, ITEM, QUANTITY, SALES
FROM OESHDT
WHERE DATE = '20190630'
GROUP BY DATE, INVNUMBER, CUSTOMER WITH ROLLUP

then I get:

Column 'OESHDT.ITEM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I just want to get the subtotals for each invoice number like this:

Date    INVNUNBER     CUSTOMER     ITEM     QTY     SALES
20190630 IN3343       joe's comp   23225    2.0     3000
20190630 IN3343       joe's comp   23214    1.0     400
                                           3.0     3400
20190630 IN3353       matt's comp. 12222     3.0     6000
20190630 IN3353       matt's comp. 32222     3.0     3000 
                                             6.0    9000

Since I'm not summing up anything and I only want subtotals for each, can SQL do this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

One option is Grouping Sets

Example

Declare @YourTable Table ([Date] varchar(50),[INVNUNBER] varchar(50),[CUSTOMER] varchar(50),[ITEM] varchar(50),[QTY] int,[SALES] int)
Insert Into @YourTable Values 
 (20190630,'IN3343','joe''s comp',23225,2.0,3000)
,(20190630,'IN3343','joe''s comp',23214,1.0,400)
,(20190630,'IN3353','matt''s comp.',12222,3.0,6000)
,(20190630,'IN3353','matt''s comp.',32222,3.0,3000)

Select Date
      ,InvNunber
      ,Customer
      ,Item
      ,Qty  = sum(Qty)
      ,Sales = sum(Sales)
 From  @YourTable
 Group By 
   Grouping Sets (
                    (Date,InvNunber,Customer,Item)
                   ,(Date,InvNunber)
                   ,(left(Date,0))
                 )
  Order By left(Date,0) Desc
          ,Date
          ,InvNunber
          ,Customer Desc

Returns

Date    InvNunber   Customer        Item    Qty Sales
20190630    IN3343  joe's comp      23214   1   400
20190630    IN3343  joe's comp      23225   2   3000
20190630    IN3343  NULL            NULL    3   3400
20190630    IN3353  matt's comp.    12222   3   6000
20190630    IN3353  matt's comp.    32222   3   3000
20190630    IN3353  NULL            NULL    6   9000
NULL        NULL    NULL            NULL    9   12400

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...