Grouping Help

jhorten2011 · #1 (**permalink**) 01-30-12, 14:53

Hi,

I need help with the grouping of results I'm receiving, so that the resultset is reduced and like Store Chains are consolidated along with their totals added together, but I'm having problems trying to get this.

My current results are:

Store Chain Amt
Walgreens Drug Store #0612 54118
Walgreens Drug Store #0613 47460
Walgreens Drug Store #0614 419134
Walgreens Drug Store #0619 12430
Wegmans Pharmacy #118 305274
Wegmans Pharmacy #119 452248
Wegmans Pharmacy #120 182486
Wegmans Pharamcy #147 97201
WEIS PHARMACY 122 724408
WEIS PHARMACY 14 146974
WEIS PHARMACY 143 631561
WEIS PHARMACY 166 419134
WEIS PHARMACY 222 631561
Weldon Pharmacy 79773
WellPartner 10707

What I need is the following results:

Store Chain Amt
<Independent> 90480
Walgreens Drug Store 533142
WEGMANS 1037209
WEIS 2553638

Here is the SQL I currently have:

Code:

DECLARE @StartDate Datetime, @EndDate Datetime
SET @StartDate = '2011-03-01'
SET @EndDate = '2011-03-01'

SELECT 
	c.Pharmacy_Name AS Chain, 
	COUNT(s.Unique_ID) AS [# Paid]	
FROM 
	Sales s
	INNER JOIN Chains c
	ON s.Chain_ID = c.Unique_ID	
WHERE 
	s.TimeStamp BETWEEN @StartDate + '00:00:00.000' AND @EndDate + '23:59:59.999'
GROUP BY 
	c.Pharmacy_Name
ORDER BY
	c.Pharmacy_Name

How can I get this query to give the results I need above? Can someone please help?

MCrowley · #2 (**permalink**) 01-30-12, 15:28

You will need to use the CASE statement to collapse Welldon, and WellPartner into one category. More importantly, you will need some rule that indicates they should be counted as <Independent>

Pat Phelan · #3 (**permalink**) 01-30-12, 15:33

The simple way to do this is to write a set of rules to reduce the [Store Chain] values from their present form to the way you want them grouped. I'd use Regular Expressions, but there are lots of other ways to do it.

Build those reductions up either as part of your code or as a UDF, and then the proces becomes trivial.

-PatP

jhorten2011 · #4 (**permalink**) 01-30-12, 15:37

I'm still learning SQL, so I really don't know how to implement that. Could you possibly provide an example of how I would integrate it into my current sql or how to group the results using CASE?

jhorten2011 · #5 (**permalink**) 01-30-12, 16:22

@MCrowley

I've modified the query with CASE statements as you suggested, but how do I get it to total all '<Independents>'?

Code:

DECLARE @StartDate Datetime, @EndDate Datetime
SET @StartDate = '2011-03-01'
SET @EndDate = '2011-03-01'

SELECT 
	CASE d.Pharmacy_Name
		WHEN 'Weldon' THEN '<Independents>'
		WHEN 'Wendell Drug Co' THEN '<Independents>'
		ELSE
			(CASE
		           WHEN c.Pharmacy_Name like 'Walgreens%' THEN 'Walgreens'
                           WHEN c.Pharmacy_Name like 'Wegmans%' THEN 'Wegmans'
                           WHEN c.Pharmacy_Name like 'WEIS%' THEN 'WEIS'
			END)
	END AS Chain, 
	COUNT(s.Unique_ID) AS [# Paid]	
FROM 
	Sales s
	INNER JOIN Chains c
	ON s.Chain_ID = c.Unique_ID	
WHERE 
	s.TimeStamp BETWEEN @StartDate + '00:00:00.000' AND @EndDate + '23:59:59.999'
GROUP BY 
	c.Pharmacy_Name
ORDER BY
	c.Pharmacy_Name

Pat Phelan · #6 (**permalink**) 01-30-12, 16:28

I would use something like:

Code:

CREATE TABLE #foo (
   Store    NVARCHAR(50)    NOT NULL
,  Amt      INT             NOT NULL
)

INSERT INTO #foo (Store, Amt)
   SELECT       'Walgreens Drug Store #0612', 54118
   UNION SELECT 'Walgreens Drug Store #0613', 47460
   UNION SELECT 'Walgreens Drug Store #0614', 419134
   UNION SELECT 'Walgreens Drug Store #0619', 12430
   UNION SELECT 'Wegmans Pharmacy #118', 305274 
   UNION SELECT 'Wegmans Pharmacy #119', 452248
   UNION SELECT 'Wegmans Pharmacy #120', 182486
   UNION SELECT 'Wegmans Pharamcy #147', 97201
   UNION SELECT 'WEIS PHARMACY 122', 724408
   UNION SELECT 'WEIS PHARMACY 14', 146974
   UNION SELECT 'WEIS PHARMACY 143', 631561
   UNION SELECT 'WEIS PHARMACY 166', 419134
   UNION SELECT 'WEIS PHARMACY 222', 631561
   UNION SELECT 'Weldon Pharmacy', 79773
   UNION SELECT 'WellPartner', 10707
GO

SELECT b.NewStore, SUM(b.Amt)
   FROM (SELECT
      CASE
         WHEN Store LIKE 'Walgreens%' THEN 'Walgreens Drug Store'
         WHEN Store LIKE 'WEGMANS%'   THEN 'WEGMANS'
         WHEN Store LIKE 'WEIS%'      THEN 'WEISe'
         ELSE '<Independant>'
      END AS NewStore
,     a.Amt
      FROM #foo AS a
      )  AS b
   GROUP BY b.NewStore
   ORDER BY b.NewStore
   
GO
DROP TABLE #foo

-PatP

jhorten2011 · #7 (**permalink**) 01-30-12, 16:44

Pat,

Thanks this is what I was after. One question though, because I have potentially over 7,000 rows that the query has to go through is there a quicker way in the temp table of getting the values, because I can't see doing 7,000 UNION SELECTs. In my initial post, this was just a tiny subset of data to illustrate what I was trying to achieve.

Pat Phelan · #8 (**permalink**) 01-30-12, 16:55

The #foo table is simply a way to store the data that you originally posted. I don't have your database handy in order to use your tables.

Just substitue the logic the I provided against your existing tables (sales and chains) instead of the #foo that I used, and you should be in business!

-PatP

jhorten2011 · #9 (**permalink**) 01-30-12, 17:48

Thank you very much for your help. This was very helpful to me!

Internet Services

Web Development

Digital Marketing

Consumer Tech