T-SQL GROUP BY ISSUE WITH CASE STATEMENT

Question

I have a query with a CASE statement and I need to do a GROUP BY on the alias. I understand that cannot be done so I tried to use a subquery, but it is not working.

Here is the query that I was working on:

SELECT
    a.Vendor,
    a.Month_Sold
    --SUM(sd.SBQSHP) AS Sales_Qty,
    --SUM(sd.SBEPRC) AS Sales_Dlr
FROM
(SELECT
    sd.IFPRVN AS Vendor,
    vn.ACNAME AS Vendor_Name,
    CASE
        WHEN sd.SBINDT BETWEEN '2012-07-30' AND '2012-08-26' THEN 'August 2012'
        WHEN sd.SBINDT BETWEEN '2012-08-27' AND '2012-09-30' THEN 'September 2012'
        WHEN sd.SBINDT BETWEEN '2012-10-01' AND '2012-10-28' THEN 'October 2012'
        WHEN sd.SBINDT BETWEEN '2012-10-29' AND '2012-11-25' THEN 'November 2012'
        WHEN sd.SBINDT BETWEEN '2012-11-26' AND '2012-12-31' THEN 'December 2012'
        WHEN sd.SBINDT BETWEEN '2013-01-01' AND '2013-01-27' THEN 'January 2013'
        WHEN sd.SBINDT BETWEEN '2013-01-28' AND '2013-02-24' THEN 'Febuary 2013'
        WHEN sd.SBINDT BETWEEN '2013-02-25' AND '2013-03-31' THEN 'March 2013'
    END AS Month_Sold
FROM
    dbo.SalesData sd
    INNER JOIN dbo.S2K_VEND vn ON vn.ACVEND = sd.IFPRVN
WHERE
    sd.SBINDT > '2012-07-29'
AND
    sd.SBCLS IN ('1500')
AND
    sd.SBDIV NOT IN ('4000')
)a
GROUP BY
    a.Vendor,
    a.Month_Sold
ORDER BY
    a.Vendor,
    a.Month_Sold

The two columns that are commented out need to be included somehow. Any suggestions?

Answer 1

Simply select the values you need to aggregate in your subquery with alias "a"

Add to subquery "a" select statement:

SELECT    
    sd.IFPRVN AS Vendor,
    vn.ACNAME AS Vendor_Name,
    sd.SBQSHP,
    sd.SBEPRC
    CASE
        ...

Uncomment SUM statements at top level select statement but change alias from "sd" to "a"

SUM(a.SBQSHP) AS Sales_Qty,
SUM(a.SBEPRC) AS Sales_Dlr

Answer 2

Really, putting pretty labels on your date output should be a job for the presentation tier. FORMAT() in C# etc. is pretty powerful. Barring that, don't convert to pretty string labels until the last possible point, which will require one more layer:

SELECT 
  Vendor, 
  DATENAME(MONTH, Month_Sold) + ' ' + RTRIM(YEAR(Month_Sold)),
  Sales_Qty,
  Sales_Dlr
FROM
(
  SELECT
    Vendor,
    Month_Sold,
    SUM(SBQSHP) AS Sales_Qty,
    SUM(SBEPRC) AS Sales_Dlr
  FROM
  (
    SELECT
      sd.IFPRVN AS Vendor,
      vn.ACNAME AS Vendor_Name, -- why is this here?
      CASE
        WHEN sd.SBINDT BETWEEN '2012-07-30' AND '2012-08-26' THEN '2012-08-01'
        WHEN sd.SBINDT BETWEEN '2012-08-27' AND '2012-09-30' THEN '2012-09-01'
        ...
        WHEN sd.SBINDT BETWEEN '2013-01-28' AND '2013-02-24' THEN '2013-02-01'
        WHEN sd.SBINDT BETWEEN '2013-02-25' AND '2013-03-31' THEN '2013-03-01'
      END AS Month_Sold
    FROM dbo.SalesData sd
      INNER JOIN dbo.S2K_VEND vn 
      ON vn.ACVEND = sd.IFPRVN
    WHERE sd.SBINDT > '2012-07-29'
    AND sd.SBCLS IN ('1500')
    AND sd.SBDIV NOT IN ('4000')
  ) AS a
  GROUP BY Vendor, Month_Sold
) AS b
ORDER BY
    Vendor,
    Month_Sold;