Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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