Solved

Subquery or Select Case (?) with Group By

Posted on 2014-01-23
15
305 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

830 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