CODEWITHSUNDEEP
sql
danish sodhi
danish sodhi

Reputation: 1913

SELECT some column and count the rows in the same query

I want to select some rows and want to return those rows plus an additional column that display the count of selected rows. I am using a derived version of sql that doesnot allow me use OVER() AS function as discussed here . I want something like for each element in selected column ,I want a count of it in entire table Like

For Initial column like this :

Fruits
Apple
Mango
Fruits
Banan

Final returned values :

Fruits NewColumnwithCount 
Apple   2
Mango   1
Apple   2
Banana  1

Like in selected colum called Fruits, Apple appear 2 times, banana 1 times and Mango 1 time (in the entire selected column called fruits)

Upvotes: 0

Views: 3607

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You are looking for window functions:

select fruit, count(*) over (partition by fruit) as NewColumnwithCount
from t;

Upvotes: 1

Josue Martinez
Josue Martinez

Reputation: 436

use a GROUP BY as:

SELECT COUNT(FruitID), FruitName FROM Fruits GROUP BY FruitName;

UDPATE

SELECT FruitName, (select count(fs.id) FROM Fruits fs where fs.id = f.id GROUP BY fs.FruitName) as 'count' FROM Fruits f;

The subquery will return how many times the selected fruit is present

Upvotes: 2

Related Questions