Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I use group by on one column which is having many entries? Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 3:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, January 2, 2014 1:30 AM
Points: 90, Visits: 199
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`.
Post #1443157
Posted Wednesday, April 17, 2013 3:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 1,079, Visits: 6,497
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)



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1443159
Posted Wednesday, April 17, 2013 3:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, January 2, 2014 1:30 AM
Points: 90, Visits: 199
I have done by using having clause

SELECT name,SUM(price) as price
FROM table
GROUP BY name
HAVING SUM(price) = 0

Thanks :)
Post #1443163
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse