Solved

Subquery or Select Case (?) with Group By

Posted on 2014-01-23
15
306 Views
Last Modified: 2014-01-24
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
Comment
Question by:amglori
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 39804863
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
 
LVL 10

Expert Comment

by:ienaxxx
ID: 39804881
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39804886
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39804892
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
 

Author Comment

by:amglori
ID: 39804923
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39804959
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
 
LVL 11

Expert Comment

by:Simone B
ID: 39804960
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
 
LVL 11

Expert Comment

by:Simone B
ID: 39804971
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39804973
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
 
LVL 11

Expert Comment

by:Simone B
ID: 39804979
sdstuber, that's very nice!
0
 

Author Comment

by:amglori
ID: 39804984
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39804987
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39804990
Did you try http:#a39804973  ?  It should include all of the data

It did when I tested it anyway
0
 

Author Closing Comment

by:amglori
ID: 39806956
This is great! Yes! Thank you!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39806976
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question