CODEWITHSUNDEEP
sql.netsql-server-2008
Neeraj Verma
Neeraj Verma

Reputation: 263

SQL : return all rows that contain particular value more than once

I have a table with the following contents:

store        Qty
----
store1       1
store2       2
store1       3
store2       2

I want to output this:

------------
store     Qty
store2    2(value '2' occurs 2 times)
store1    0(value '2' occurs 0 times)

I want to return occurrence of column Qty with value '2' (with how many times it occured) in descending order.

Upvotes: 0

Views: 79

Answers (2)

JMariña
JMariña

Reputation: 324

SELECT t.store, COUNT(QTY) AS QTY2
FROM TABLE T
WHERE t.QTY = 2
GROUP BY t.store
ORDER BY COUNT(QTY) DESC

This should work. It will provide a count of how many 2's are present grouped by store and show the stores in descending order based on the number of 2's present.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You want conditional aggregation (that is, using a case statement with an aggregation function):

select store, sum(case when Qty = '2' then 1 else 0 end) as Qty
from table t
group by store;

Upvotes: 5

Related Questions