CODEWITHSUNDEEP
mysqlsql
Sjoerd de Wit
Sjoerd de Wit

Reputation: 2413

Sql count with inner join and distinct values

i'm currently working on a query and I want to return rows from multiple tables and these need to be distinct. But while I only want 1 distinct row to return, I would also like to know how many rows there are without having to do another query, This is what i got so far: 

SELECT DISTINCT a.title,
                a.lat,
                a.lon,
                a.alert_content_id,
                a.date_added,
                count(*) 
FROM `reply` r 
INNER JOIN `alerts` a ON r.alert_id=a.alerts 
WHERE `user` = '1'

This one counts how many rows there are in reply and returns only one row. but it needs to be rows that have this where claus: r.alert_id=a.alerts

this returns all the rows but misses the count for each individual row 

SELECT DISTINCT a.title,
                a.lat,
                a.lon,
                a.alert_content_id,
                a.date_added 
FROM `reply` r 
INNER JOIN `alerts` a ON r.alert_id=a.alerts 
WHERE `user` = '1'

There was an answer but it didn't quite return what i needed, i need to count all the reply's from the alert the user has commented on, not just count the number of times that user commented on that alert.

Could someone point me in the right direction? Or provide an answer? Thx.

Upvotes: 0

Views: 4689

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Is this what you want?

SELECT a.title,
       a.lat,
       a.lon, 
       a.alert_content_id, 
       a.date_added, 
       count(*)
FROM `reply` r INNER JOIN
     `alerts` a
      ON r.alert_id = a.alerts
WHERE `user` = '1'
GROUP BY a.title, a.lat, a.lon, a.alert_content_id, a.date_added;

The answer to your question is that you don't want to use select distinct. You want to make an aggregation query that uses group by. It is just unclear what you want to put into the group by clause.

Upvotes: 4

Related Questions