CODEWITHSUNDEEP
mysqlsql
arsarc
arsarc

Reputation: 453

Sql Count the occurances while still having all of the row Values

This was a request from someone in my department, but I'm having trouble writing to have both the total amount of occurrences in a column but also keep the rows.

For sake of simplicity, a table like this:

Name    Location
Dave      Ohio
Sam       Ohio 
James    Virginia 
Fred     Idaho 
Cindy   Virginia 
John     Ohio

But I need it to look like this:

Name   Location   Total 
Dave   Ohio        3
Sam    Ohio        3
John   Ohio        3
James  Virginia    2
Cindy  Virginia    2
Fred   Idaho       1

So my attempt:

select Name, Location, count(Location) as 'Total'
from table1
group by Location

Gives me

Name    Location    Total
Fred    Idaho         1
Dave    Ohio          1
John    Ohio          1
Sam     Ohio          1
Cindy   Virginia      1
James   Virginia      1

Upvotes: 0

Views: 19

Answers (2)

D-Shih
D-Shih

Reputation: 46229

You can try this, write a subquery to get COUNT by Location then self JOIN

SELECT 
    t.Name,t.Location,t1.Total
FROM 
    T t 
INNER JOIN 
    (SELECT Location,count(*) Total
     FROM T
     GROUP BY Location) t1 ON t.Location = t1.Location
ORDER BY 
    t1.Total DESC

SQLfiddle:http://sqlfiddle.com/#!9/cef8cf/3

Result

|  Name | Location | Total |
+-------+----------+-------+
|   Sam |     Ohio |     3 |
|  John |     Ohio |     3 |
|  Dave |     Ohio |     3 |
| James | Virginia |     2 |
| Cindy | Virginia |     2 |
|  Fred |    Idaho |     1 |

Upvotes: 1

tourist
tourist

Reputation: 4333

You can use window functions

select distinct name, location, count(*) over(partition by location) 
from table1

This works from MySQL >= 8

Upvotes: 1

Related Questions