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
– Zohar Peled, Commented Jul 2, 2017 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
– danish sodhi, Commented Jul 2, 2017 at 15:12

Josue Martinez · Accepted Answer · 2017-07-02 15:18:37Z

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 2, 2017 at 15:18

answered Jul 2, 2017 at 15:11

Josue Martinez

4365 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

danish sodhi Over a year ago

But this will group all Apples together , no ? I want each time apple appears as it is, with additional information of the count

Josue Martinez Over a year ago

ok I get it now...then use a sub-query for the count. Check the updated answer

Gordon Linoff · Accepted Answer · 2017-07-02 15:48:53Z

1

You are looking for window functions:

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

answered Jul 2, 2017 at 15:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SELECT some column and count the rows in the same query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related