Compute & Rollup

• kd11

  Hall of Fame

  Points: 3447

  More actions

  August 26, 2018 at 12:07 pm

  #378490

  This statement will produce:  Select EmpID, Yr, sum(Sales)as Sales from Sales Group by EmpId, Yr With Rollup

   EmpID               Yr                            Sales
       1                  2014                            120
       1                  2015                            180
       1                  2016                            250
       1                  null                             550
       2                2014                            150
       2                2012                              60
       2                2013                             210
       2               null                                420
       3               2018                               20
       3               2017                             440
       3               2015                              130
       3              null                                590

  How can I change the statement above to produce only, I guess read it into a temp table and select from the temp table where the year is equal to 'null'

  1                  null                             550
   2               null                                420
   3              null                                590

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  August 26, 2018 at 6:56 pm

  #2003378

  kd11 - Sunday, August 26, 2018 12:07 PM

  This statement will produce:  Select EmpID, Yr, sum(Sales)as Sales from Sales Group by EmpId, Yr With Rollup

   EmpID               Yr                            Sales
       1                  2014                            120
       1                  2015                            180
       1                  2016                            250
       1                  null                             550
       2                2014                            150
       2                2012                              60
       2                2013                             210
       2               null                                420
       3               2018                               20
       3               2017                             440
       3               2015                              130
       3              null                                590

  How can I change the statement above to produce only, I guess read it into a temp table and select from the temp table where the year is equal to 'null'

  1                  null                             550
   2               null                                420
   3              null                                590

  WHERE GROUPING(EmpID) = 1

  Haven't tested it though it should do the job.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• drew.allen

  SSC Guru

  Points: 76989

  More actions

  August 27, 2018 at 9:43 am

  #2003457

  kd11 - Sunday, August 26, 2018 12:07 PM

  This statement will produce:  Select EmpID, Yr, sum(Sales)as Sales from Sales Group by EmpId, Yr With Rollup

   EmpID               Yr                            Sales
       1                  2014                            120
       1                  2015                            180
       1                  2016                            250
       1                  null                             550
       2                2014                            150
       2                2012                              60
       2                2013                             210
       2               null                                420
       3               2018                               20
       3               2017                             440
       3               2015                              130
       3              null                                590

  How can I change the statement above to produce only, I guess read it into a temp table and select from the temp table where the year is equal to 'null'

  1                  null                             550
   2               null                                420
   3              null                                590

  You should only use ROLLUP if you want multiple levels of a hierarchy.  Here you only want it based on EmpID, so only use EmpID.
  Select EmpID, CAST(NULL AS INT) AS Yr, sum(Sales)as Sales from Sales Group by EmpId

  Also, WITH ROLLUP has been deprecated in favor of GROUPING SETS(), which I believe were introduced in SQL 2008.  You should be using GROUPING SETS() instead of WITH ROLLUP().

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  August 27, 2018 at 12:12 pm

  #2003501

  drew.allen - Monday, August 27, 2018 9:43 AM

  kd11 - Sunday, August 26, 2018 12:07 PM

  This statement will produce:  Select EmpID, Yr, sum(Sales)as Sales from Sales Group by EmpId, Yr With Rollup

   EmpID               Yr                            Sales
       1                  2014                            120
       1                  2015                            180
       1                  2016                            250
       1                  null                             550
       2                2014                            150
       2                2012                              60
       2                2013                             210
       2               null                                420
       3               2018                               20
       3               2017                             440
       3               2015                              130
       3              null                                590

  How can I change the statement above to produce only, I guess read it into a temp table and select from the temp table where the year is equal to 'null'

  1                  null                             550
   2               null                                420
   3              null                                590

  You should only use ROLLUP if you want multiple levels of a hierarchy.  Here you only want it based on EmpID, so only use EmpID.
  Select EmpID, CAST(NULL AS INT) AS Yr, sum(Sales)as Sales from Sales Group by EmpId

  Also, WITH ROLLUP has been deprecated in favor of GROUPING SETS(), which I believe were introduced in SQL 2008.  You should be using GROUPING SETS() instead of WITH ROLLUP().

  Drew

  While I absolutely agree with that notion, I can see people using common code in a view or function and then picking out the totals that they want.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  August 28, 2018 at 11:35 am

  #2003669

  kd11 - Sunday, August 26, 2018 12:07 PM

  This statement will produce:  Select EmpID, Yr, sum(Sales)as Sales from Sales Group by EmpId, Yr With Rollup

   EmpID               Yr                            Sales
       1                  2014                            120
       1                  2015                            180
       1                  2016                            250
       1                  null                             550
       2                2014                            150
       2                2012                              60
       2                2013                             210
       2               null                                420
       3               2018                               20
       3               2017                             440
       3               2015                              130
       3              null                                590

  How can I change the statement above to produce only, I guess read it into a temp table and select from the temp table where the year is equal to 'null'

  1                  null                             550
   2               null                                420
   3              null                                590

  SELECT X.emp_id, X.sale_yr, X.sales_amt
FROM (SELECT emp_id, sale_yr, sales_amt
      FROM Sales
     GROUP BY ROLLUP( emp_id, sale_yr))
     AS X (emp_id, sale_yr, sales_amt)
WHERE X.sale_yr IS NULL;

  untested.

  Please post DDL and follow ANSI/ISO standards when asking for help. 

• drew.allen

  SSC Guru

  Points: 76989

  More actions

  August 28, 2018 at 1:28 pm

  #2003714

  Jeff Moden - Sunday, August 26, 2018 6:55 PM

  kd11 - Sunday, August 26, 2018 12:07 PM

  This statement will produce:  Select EmpID, Yr, sum(Sales)as Sales from Sales Group by EmpId, Yr With Rollup

   EmpID               Yr                            Sales
       1                  2014                            120
       1                  2015                            180
       1                  2016                            250
       1                  null                             550
       2                2014                            150
       2                2012                              60
       2                2013                             210
       2               null                                420
       3               2018                               20
       3               2017                             440
       3               2015                              130
       3              null                                590

  How can I change the statement above to produce only, I guess read it into a temp table and select from the temp table where the year is equal to 'null'

  1                  null                             550
   2               null                                420
   3              null                                590

  WHERE GROUPING(EmpID) = 1

  Haven't tested it though it should do the job.

  This needs to be HAVING rather than WHERE.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  August 29, 2018 at 3:01 pm

  #2003908

  Assuming you don't want the overall total that WITH ROLLUP would give you.
  
Select
 EmpID,
 Yr,
 Sum(Sales)
From @Table
Group By Grouping Sets ((EmpID,Yr),(EmpID))
Having Grouping_ID(EmpId, Yr) = 1


  Although I'm not sure why you do that over:
  
Select
 EmpID,
 null Yr,
 Sum(Sales)
From @Table
Group By EmpID

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  August 29, 2018 at 3:51 pm

  #2003922

  drew.allen - Tuesday, August 28, 2018 1:28 PM

  This needs to be HAVING rather than WHERE.

  Drew

  Correct.  My apologies.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)