Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Answered: Group By Total and Grand Total on same row?

    I have a dataset on unit sales by SKU separated by Retail and Direct order method. I am trying to output sales by sku total, and retail on the same row. I can do this with some self joins but am wondering if there is an easier way that I am not familiar with. The actual query I am working with is much more complicated with many table joins and WHERE criteria, hence the desire to avoid more joins and end up creating a monster frankenquery.

    Sample:
    Code:
    	
    create table #myTable(
    	Item varchar(2)
    	,OrdMeth varchar(10)
    	,Demand int
    	,Units int);
    INSERT INTO #myTable
    VALUES ('AA', 'Direct', 168.0000, 20)
    		,('AA', 'Retail', 20.0000, 2)
                    ,('BB', 'Direct', 100.0000, 10)
                    ,('BB', 'Retail', 40.0000, 4)
    My method:
    Code:
    SELECT isnull(a.Item,b.Item) AS Item,a.TotalUnits,b.RetailUnits
    FROM
    	(SELECT Item,sum(units) AS TotalUnits
    	FROM #myTable
    	GROUP BY Item) a
    INNER JOIN 
    	(SELECT Item,sum(units) AS RetailUnits
    	FROM #myTable
    	WHERE ordmeth = 'retail'
    	GROUP BY Item) b
    ON a.item = b.item

  2. Best Answer
    Posted by gvee

    "
    Code:
    SELECT Item
         , Sum(units) AS TotalUnits
         , Sum(CASE WHEN OrdMeth = 'Retail' THEN units ELSE 0 END) AS RetailUnits
    FROM   #myTable
    GROUP
        BY Item
    "


  3. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Item
         , Sum(units) AS TotalUnits
         , Sum(CASE WHEN OrdMeth = 'Retail' THEN units ELSE 0 END) AS RetailUnits
    FROM   #myTable
    GROUP
        BY Item
    George
    Home | Blog

  4. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by gvee View Post
    Code:
    SELECT Item
         , Sum(units) AS TotalUnits
         , Sum(CASE WHEN OrdMeth = 'Retail' THEN units ELSE 0 END) AS RetailUnits
    FROM   #myTable
    GROUP
        BY Item
    Ah, that's great, thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •