none
grouping ,more than one way in a query RRS feed

  • Question

  • Hi we run 2014 enterprise.  Can a single query take two or more sums on various columns using different groupings?  In one pass of the table and without special help from sub selects, joins etc?
    Thursday, December 3, 2015 6:40 PM

Answers

All replies

  • You can use grouping sets.

    http://blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, December 3, 2015 6:55 PM
  • Depending on exactly what you want, you could also use SUM Over Partitions, for example

    Declare @Test Table(City varchar(50), Department int, Sales int);
    Insert @Test(City, Department, Sales) Values
    ('New York', 7, 25),
    ('New York', 8, 100),
    ('New York', 9, 20),
    ('LA', 7, 13),
    ('LA', 9, 10);
    
    Select City, Department, Sales,
      Sum(Sales) Over(Partition By City) As TotalCitySales,
      Sum(Sales) Over(Partition By Department) As TotalDepartmentSales
    From @Test t;

    If the suggestions you recieve don't help you, please provide sample tables and data and tell us the result you would want from that sample data.  That will help us give you a better answer.

    Tom

    Thursday, December 3, 2015 7:06 PM
  • Try to use window functions with PARTITION BY clause for different groups.
    Thursday, December 3, 2015 7:08 PM
    Moderator
  • thx all.  Russ, I have to get my head around your link.  I'll reread it.

    Tom and Jingyang, what if in addition to what you showed, I want some groupings on a subset where a certain flag is 'N' , another where it is 'Y' and a third where it is either?  But each returned in the same row?  Do I need a pivot?

    Thursday, December 3, 2015 7:31 PM
  • You really need to post your table DDL and some sample data. It will save your time and others' as well. Thanks.
    Thursday, December 3, 2015 7:49 PM
    Moderator
  • You can use case based pivot. See this helpful blog post

    Understanding SQL Server 2000 Pivot with Aggregates

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, December 3, 2015 8:55 PM
    Moderator
  • here is a table...what if from the same query , same pass I want

    1) sum of sales by group where pending is N and qtr is 3 and year is 2015,

    2) same as latter where pending is Y

    3) sum of sales where year is 2015 regardless of pending status

    4) count distinct of customers for 2015 regardless of pending status

    5) count distinct of customers for 2015 by group regardless of pending status

    6) sum sales where qtr is 3, year is 2015 regardless of anything else

    USE [whatever]
    GO
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[sales](
    	[id] int identity(1,1) NOT NULL,
    	[sales] decimal(20,9) NOT NULL,
    	[pendingStatus] char(1) not null, --Y/N
    	[salesGroup] varchar(50),
    	[customer] varchar(50),
    	[year] smallint not null,
    	[qtr] tinyint not null,
    	
     CONSTRAINT [X] PRIMARY KEY NONCLUSTERED 
    (
    	[id] ASC...

    Thursday, December 3, 2015 9:08 PM
  • select sum(case when PendingStatus = 'N' and qtr = 3 then Sales end) as [Completed Sales 3rd Quarter],
    
    sum(case when PendingStatus = 'Y' and qtr = 3 then Sales end) as [Pending Sales 3rd Quarter],
    
    SUM(Sales) as TotalSales,
    
    COUNT(distinct Customer) as [Customers Count],
    
    SUM(case when qtr= 3 then Sales end) as [Sales in 3rd Quarter],
    
    salesGroup
    
    from dbo.Sales
    
    WHERE [Year] = 2015
    
    GROUP BY salesGroup



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, December 3, 2015 10:37 PM
    Moderator
  • All the above information will be based on SalesGroup. If you also want to include totals regardless of the group, I suggest to run a separate query for totals and join with it, e.g.

    ;with cte1 as (my First query),

    cte2 as (my First query but without SalesGrp to get totals only - use different names for the columns)

    select cte1.*, cte2.* (cte2 is a single row result so we use a cross join) 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 3, 2015 10:42 PM
    Moderator
  • here is a table...what if from the same query , same pass I want

    1) sum of sales by group where pending is N and qtr is 3 and year is 2015,

    2) same as latter where pending is Y

    3) sum of sales where year is 2015 regardless of pending status

    4) count distinct of customers for 2015 regardless of pending status

    5) count distinct of customers for 2015 by group regardless of pending status

    6) sum sales where qtr is 3, year is 2015 regardless of anything else

    USE [whatever]
    GO
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[sales](
    	[id] int identity(1,1) NOT NULL,
    	[sales] decimal(20,9) NOT NULL,
    	[pendingStatus] char(1) not null, --Y/N
    	[salesGroup] varchar(50),
    	[customer] varchar(50),
    	[year] smallint not null,
    	[qtr] tinyint not null,
    	
     CONSTRAINT [X] PRIMARY KEY NONCLUSTERED 
    (
    	[id] ASC...

    Hi we run 2014 enterprise.  Can a single query take two or more sums on various columns using different groupings?  In one pass of the table and without special help from sub selects, joins etc?

    The sample and desired aggregates shows that you want to apply more than one aggregate function in one query with multiple where clause conditions. This can be done by using sub-queries. Sounds like you don't know about how a SQL query processes. When you want to filter the select query by ( pendingStatus = N'N' ),  so you have to put this condition in your where clause. Then you cannot access the rows with ( pendingStatus = N'Y' ) condition, unless you use a sub-query. Please take a look at Logical Processing Order of the SELECT statement in https://msdn.microsoft.com/en-us/library/ms189499.aspx



    Saeid Hasani (My Writings on TechNet Wiki ,T-SQL Blog)


    • Edited by Saeid Hasani Thursday, December 3, 2015 10:56 PM
    Thursday, December 3, 2015 10:51 PM
  • Thx all.  Russ when using grouping sets, cube etc, is sql server making only one pass thru the table?  

    This seems to be the most elegant/reusable/maintainable way of doing this in spite of the fact that i'd need a pivot and have to offer additional help in naming columns.

    Friday, December 4, 2015 6:09 PM
  • What do you mean by 1 pass? The query is going to be efficient but the number of reads you can determine if running with SET STATISTICS IO ON statement before running your query.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, December 4, 2015 6:15 PM
    Moderator
  • I did a quick test.  It appears that the query will read the base table once.  In what I tried, it created a structure that SQL had to read three times.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, December 4, 2015 6:29 PM
  • thx Russ, is that a structure in "memory"? a physical work table?  r u implying that its not necessarily more efficient than the alternatives?

    Also, any thoughts Russ on how this class of command can be leveraged to get distinct counts in the same query that groups $'s ?...  keeping in mind (for example) that the sum of distinct customer counts in 2015 is not necessarily equal to the sum of distinct 2015 customer counts grouped by sales.

    Friday, December 4, 2015 6:53 PM
  • The execution plan and statistics io showed a worktable.  I don't know if SQL writes to a temp table or if SQL handles that in memory.  I would assume that the resulting worktable is considerably smaller than the original table.

    I'm not exactly sure I understand your follow-up question.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, December 4, 2015 7:16 PM
  • thx.  I'm going to reread your link and see if I can re word that follow up question more clearly.  It may not be today EST.
    Friday, December 4, 2015 7:25 PM
  • thx Naomi, it looks like u don't need the 2nd occurrence of the word "select"
    Monday, December 7, 2015 1:38 PM
  • Sure, it was copy and paste typo. I corrected the code.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, December 7, 2015 4:54 PM
    Moderator