CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
WeekendCoder
WeekendCoder

Reputation: 1035

SQL: COUNT() grouped results

this is my current query:

SELECT locationname, eventid
FROM events
GROUP BY locationname
ORDER BY locationname ASC

As you can see it's grouped by locationname because there are several rows with the same locationname.

In the output i just need a list of "distinct" (grouped) locations but behind every location there should be the total amount of each location.

So if in "events" are 4 locationsname with "Congress Center NYC" the output should be "Congress Center NYC (4)".

Is there a way to expand the query with a COUNT()?

Thanks!

Upvotes: 0

Views: 75

Answers (4)

Periyasamy Balaji
Periyasamy Balaji

Reputation: 1

SELECT locationname, locationname+quotename(COUNT(distinct locationname),'()')
FROM events GROUP BY locationname

Please let me know in comments if it didn't work Note: Before post the qn Please mention which kind of database you are asked for

Upvotes: 0

2oppin
2oppin

Reputation: 1991

In your query example there is "event_id", when you add grouping you will receive "event_id" of the first occurred row with the same "locationame", you can use group_concat to get all events and perform count in the same time 

SELECT locationname, group_concat(eventid), count(eventid) as number
FROM events
GROUP BY locationname
ORDER BY locationname ASC

also you can use concat function to have the output exactly as you wrote

SELECT concat(locationname, ' (', count(*), ')')
FROM events
GROUP BY locationname
ORDER BY locationname ASC

Upvotes: 0

PeteyPii
PeteyPii

Reputation: 379

Selecting COUNT(locationname) should do what you want. I.e.

SELECT locationname, COUNT(locationname)
FROM events
GROUP BY locationname
ORDER BY locationname ASC

Upvotes: 2

O. Jones
O. Jones

Reputation: 108776

This is a straightforward aggregate query.

SELECT locationname, COUNT(*) number
  FROM events
 GROUP BY locationname
 ORDER BY locationname

If you want specific formatting, you can get it using this first line of your query.

 SELECT CONCAT(locationname, ' (', COUNT(*), ')')

Upvotes: 3

Related Questions