• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Subquery or Select Case (?) with Group By

I have an existing stored proc I have to modify. It has a "group by" clause in it that's fouling up what I need to do. This isn't the exact situation, but it's an example that illustrates what I'm trying to do. I know I'm missing something obvious.

Given example  table data:

name	equipment_type	qty	price
Baseball	Baseball	20	5.99
Baseball Bat	Baseball	20	22.99
Baseball Glove	Baseball	20	11.99
Basketball	Basketball	10	11.99
Volleyball	Volleyball	4	8.99
Soccer Ball	Soccer	15	10.99
Soccer Net	Soccer	6	89.50

Open in new window


And an existing query like this:

SELECT equipment_type
	, SUM(qty) as 'qty'
	, AVG(price) as 'avg_price'
FROM my_table
GROUP BY equipment_type
ORDER BY equipment_type 

Open in new window


I need to add to the SELECT something to select SUM(qty) of ONLY the items where price > $10.00. How can I do this? I feel like it's obvious and I'm over-thinking it. TIA!
0
amglori
Asked:
amglori
  • 5
  • 4
  • 3
  • +2
1 Solution
 
Simone BSenior E-Commerce AnalystCommented:
Try this:

SELECT equipment_type
	, SUM(qty) as 'qty'
	, AVG(price) as 'avg_price'
FROM my_table
WHERE price > 10
GROUP BY equipment_type
ORDER BY equipment_type 

Open in new window

0
 
ienaxxxCommented:
if i don't misunderstood:

SELECT equipment_type
	, SUM(qty) as 'qty'
	, AVG(price) as 'avg_price'
FROM my_table
WHERE price > 10.00
GROUP BY equipment_type
ORDER BY equipment_type 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think the above is what you need, but just in case you wonder, and want to "sum up" all those into 1 single line of which the price is > 10 ...
SELECT case when price > 10 then '<expensive_stuff>' else equipment_type end equipment_type
	, SUM(qty) as 'qty'
	, AVG(price) as 'avg_price'
FROM my_table
GROUP BY case when price > 10 then '<expensive_stuff>' else equipment_type end
ORDER BY case when price > 10 then '<expensive_stuff>' else equipment_type end
                                  

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sdstuberCommented:
Do you want the filter before or after the average?
-- before
SELECT equipment_type
      , SUM(qty) as 'qty'
      , AVG(price) as 'avg_price'
FROM my_table
WJERE price > 10
GROUP BY equipment_type
ORDER BY equipment_type

-- after
SELECT equipment_type
      , SUM(qty) as 'qty'
      , AVG(price) as 'avg_price'
FROM my_table
GROUP BY equipment_type
HAVING AVG(price) > 10
ORDER BY equipment_type
0
 
amgloriAuthor Commented:
None of these quite work... I think I didn't explain myself quite right.

I cannot use the WHERE clause because 'qty' should show TOTAL quantity (no matter what price), and 'avg_price' should show average price (no matter what price), then ALSO need qty of JUST those items over $10.

So, using the example data from my initial question, what I need to return would be something like:

equipment_type		total_qty	avg_price		qty_over_10_dollars
Baseball		60		13.656666		40
Basketball		10		11.990000		10
Soccer			21		50.245000		21
Volleyball		4		8.990000		0

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT equipment_type
      , SUM(qty) as 'qty'
      , AVG(price) as 'avg_price'
       , SUM(case when price >= 10 then qty else 0 end) qty_over_10
FROM my_table
WHERE price > 10.00
GROUP BY equipment_type
ORDER BY equipment_type
0
 
Simone BSenior E-Commerce AnalystCommented:
You could use a subquery, and join by equipment_type. Since you're grouping by equipment_type, it will be unique in both queries.

SELECT a.equipment_type
	, SUM(a.qty) as 'qty'
	, AVG(a.price) as 'avg_price'
	, b.Total_10
FROM my_table a
INNER JOIN 
	(SELECT equipment_type, SUM(price) AS Total_10
	FROM my_table
	WHERE price > 10.00
	GROUP BY equipment_type) b
ON a.equipment_type = b.equipment_type
GROUP BY equipment_type
ORDER BY equipment_type

Open in new window

0
 
Simone BSenior E-Commerce AnalystCommented:
If you have any equipment_types that don't have any prices greater than $10, then a left join would be a better idea:

SELECT a.equipment_type
	, SUM(a.qty) as 'qty'
	, AVG(a.price) as 'avg_price'
	, b.Total_10
FROM my_table a
LEFT OUTER JOIN 
	(SELECT equipment_type, SUM(price) AS Total_10
	FROM my_table
	WHERE price > 10.00
	GROUP BY equipment_type) b
ON a.equipment_type = b.equipment_type
GROUP BY equipment_type
ORDER BY equipment_type

Open in new window

0
 
sdstuberCommented:
no need to self join, just query the table once is sufficient


SELECT equipment_type,
         SUM(qty) AS total_qty,
         AVG(price) AS avg_price,
         coalesce(SUM(CASE WHEN price > 10 THEN qty END),0) qty_over_10_dollars
    FROM my_table
GROUP BY equipment_type
ORDER BY equipment_type
0
 
Simone BSenior E-Commerce AnalystCommented:
sdstuber, that's very nice!
0
 
amgloriAuthor Commented:
We're moving in the right direction - but both these solutions only give me back numbers for
Baseball
Basketball
Soccer

(The 3 that HAVE values > 10)

If it helps, here's an easy way to create/test the table:

CREATE TABLE #temp ([name] varchar(50), [equipment_type] varchar(50), [qty] int, [price] decimal(18,2))

INSERT INTO #temp  (name, equipment_type, qty, price) values ('Baseball', 'Baseball', 20, 5.99)
INSERT INTO #temp  (name, equipment_type, qty, price) values ('Baseball Bat', 'Baseball', 20, 22.99)
INSERT INTO #temp  (name, equipment_type, qty, price) values ('Baseball Glove', 'Baseball', 20, 11.99)
INSERT INTO #temp  (name, equipment_type, qty, price) values ('Basketball', 'Basketball', 10, 11.99)
INSERT INTO #temp  (name, equipment_type, qty, price) values ('Volleyball', 'Volleyball', 4, 8.99)
INSERT INTO #temp  (name, equipment_type, qty, price) values ('Soccer Ball', 'Soccer', 15, 10.99)
INSERT INTO #temp  (name, equipment_type, qty, price) values ('Soccer Net', 'Soccer', 6, 89.50)

SELECT equipment_type
	, SUM(qty) as 'qty'
	, AVG(price) as 'avg_price'
	--, SUM(qty) as 'qty_over_10_dollars' -- HELP!?
FROM #temp
GROUP BY equipment_type
ORDER BY equipment_type

DROP TABLE #temp

Open in new window

0
 
sdstuberCommented:
oops, just noticed angeliii already had a similar post above

I don't think the where clause is wanted though
You'll lose volleyball and part of baseball  if you do that
0
 
sdstuberCommented:
Did you try http:#a39804973  ?  It should include all of the data

It did when I tested it anyway
0
 
amgloriAuthor Commented:
This is great! Yes! Thank you!
0
 
sdstuberCommented:
a split with angeliii would probably be appropriate.

we both posted basically the same thing but he did first
I took out the where clause and added the coalesce to get 0 but same idea
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now