How do I use group by on one column which is having many entries?

Question

How do I use group by on one column which is having many entries?

ashuthinks

SSCommitted

Points: 1698
More actions
April 17, 2013 at 3:34 am

#394610

Hi,
I want to use group by on one column which is having many entries
table_a
Name price
AAA 12
BBB 13
AAA 0
CCC 24
AAA 0
DDD 0
Now I want to find out `Name` which is having `Price` as `0`
but as I'm having entries `AAA` 3 times I can't directly write simple sql with condition
`NOT Equal to 0`
Please help me I want to print result for above table_a should be
only `D` as it is having `0` as `price`.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

ashuthinks (4/17/2013)
Hi,
I want to use group by on one column which is having many entries
table_a
Name price
AAA 12
BBB 13
AAA 0
CCC 24
AAA 0
DDD 0
Now I want to find out `Name` which is having `Price` as `0`
but as I'm having entries `AAA` 3 times I can't directly write simple sql with condition
`NOT Equal to 0`
Please help me I want to print result for above table_a should be
only `D` as it is having `0` as `price`.

SELECT 1 FROM table_a a WHERE NOT EXISTS (SELECT 1 FROM table_a b WHERE b.name = a.name AND b.Price > 0)

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ashuthinks SSCommitted Points: 1698 More actions · Answer 2

I have done by using having clause

SELECT name,SUM(price) as price

FROM table

GROUP BY name

HAVING SUM(price) = 0

Thanks 🙂