Solved

Subquery or Select Case (?) with Group By

Posted on 2014-01-23
15
302 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 142

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
 
LVL 73

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 142

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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 73

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 73

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 73

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 73

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now