SELECT some column and count the rows in the same query

Question

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)

I can't understand the connection between your sample data and your desired results. — Zohar Peled, Jul 02 '17 at 15:11
@ZoharPeled , Like in selected colum called Fruits, Apple appear 2 times, banana 1 times and Mango 1 time (in the entire selected column called fruits) — danish sodhi, Jul 02 '17 at 15:12

Josue Martinez · Answer 1 · 2017-07-02T15:18:37.310

2

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

edited Jul 02 '17 at 15:18

answered Jul 02 '17 at 15:11

Josue Martinez

436
5
14

But this will group all Apples together , no ? I want each time apple appears as it is, with additional information of the count – danish sodhi Jul 02 '17 at 15:14
ok I get it now...then use a sub-query for the count. Check the updated answer – Josue Martinez Jul 02 '17 at 15:15

score 1 · Answer 2 · answered Jul 02 '17 at 15:48

1

You are looking for window functions:

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

answered Jul 02 '17 at 15:48

Gordon Linoff

1,242,037
58
646
786

SELECT some column and count the rows in the same query

2 Answers2