Results 1 to 9 of 9

Thread: Grouping Help

  1. #1
    Join Date
    Jan 2012
    Posts
    24

    Unanswered: Grouping Help

    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?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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>

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2012
    Posts
    24
    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?

  5. #5
    Join Date
    Jan 2012
    Posts
    24
    @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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2012
    Posts
    24
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2012
    Posts
    24
    Thank you very much for your help. This was very helpful to me!

Posting Permissions

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