Unexpected results in MySQL count query for cities

Other Questions MySQL
1

I’m running into a weird issue with a MySQL query. I’m trying to count concerts in different cities, but the numbers don’t make sense.

Here’s my query:

SELECT DISTINCT l.city, COUNT(*)
FROM concerts c
JOIN venues v ON c.venue_id = v.id
JOIN locations l ON v.location_id = l.id
JOIN artists a ON c.artists = a.id
JOIN user_concerts uc ON c.id = uc.concertid
WHERE c.date BETWEEN '1985-01-01' AND '2021-06-24'
  AND uc.userid = 1
GROUP BY l.city

The results show most cities have a count of 15, which seems off. Halifax has 105 and a couple others have 30. I expected each city to have a count of 1.

Any ideas why this is happening? Am I missing something obvious in my query?

2

hey there, i think i see the issue. you’re using COUNT(*) which counts all rows, including duplicates. try COUNT(DISTINCT c.id) instead to count unique concerts. also, make sure your joins aren’t creating unintended duplicates. hope this helps!

3

The problem lies in your use of COUNT(*) combined with multiple joins. This setup counts all rows produced by the joins, leading to inflated numbers. To fix this, you should use COUNT(DISTINCT c.id) instead.

Additionally, consider the necessity of each join. The DISTINCT keyword in your SELECT clause isn’t having the effect you might expect due to the GROUP BY. If you only need to count concerts, you might not need all these joins.

Here’s a revised query that should give you the correct count:

SELECT l.city, COUNT(DISTINCT c.id)
FROM concerts c
JOIN venues v ON c.venue_id = v.id
JOIN locations l ON v.location_id = l.id
JOIN user_concerts uc ON c.id = uc.concertid
WHERE c.date BETWEEN '1985-01-01' AND '2021-06-24'
  AND uc.userid = 1
GROUP BY l.city

This should provide the accurate count of concerts per city for the specified user.

4

I’ve encountered similar issues in my database work. The problem stems from the multiple joins creating duplicate rows, which COUNT(*) happily tallies up. To fix this, you need to count distinct concerts instead.

Try modifying your query like this:

SELECT l.city, COUNT(DISTINCT c.id) as concert_count
FROM concerts c
JOIN venues v ON c.venue_id = v.id
JOIN locations l ON v.location_id = l.id
JOIN user_concerts uc ON c.id = uc.concertid
WHERE c.date BETWEEN '1985-01-01' AND '2021-06-24'
  AND uc.userid = 1
GROUP BY l.city

This should give you the correct count per city. The DISTINCT keyword in your original SELECT was ineffective due to the GROUP BY clause. Also, consider whether you need all those joins - the artists table isn’t used in the final result, so you could remove that join to simplify the query further.

Let me know if this solves your issue!

5

I’ve run into this issue before. The problem is that COUNT(*) counts all rows from your joins, not just unique concerts. This inflates your numbers, especially with multiple joins.

To fix it, use COUNT(DISTINCT c.id) instead. This ensures you’re counting unique concerts, not duplicate rows from joins.

Also, you can simplify your query by removing unnecessary joins. The artists table isn’t used in your final output, so you can drop that join.

Here’s a revised query that should work:

SELECT l.city, COUNT(DISTINCT c.id) as concert_count
FROM concerts c
JOIN venues v ON c.venue_id = v.id
JOIN locations l ON v.location_id = l.id
JOIN user_concerts uc ON c.id = uc.concertid
WHERE c.date BETWEEN '1985-01-01' AND '2021-06-24'
  AND uc.userid = 1
GROUP BY l.city

This should give you the correct count of concerts per city for the specified user. Let me know if you need any clarification.

6

yo, i think i kno whats up. ur query’s countin all rows from joins, not just concerts. try this:

SELECT l.city, COUNT(DISTINCT c.id) as concert_count
FROM concerts c
JOIN venues v ON c.venue_id = v.id
JOIN locations l ON v.location_id = l.id
JOIN user_concerts uc ON c.id = uc.concertid
WHERE c.date BETWEEN ‘1985-01-01’ AND ‘2021-06-24’
AND uc.userid = 1
GROUP BY l.city

this should fix it. lemme kno if it works!