CODEWITHSUNDEEP
sqlselectcount
Mireille28
Mireille28

Reputation: 337

Count combined with select distinct

actually i try to find a way to select all distinct private message from a table and also got the count of each in 1 sql query

the actual table have column with the id of the member and also a column with the actual view state of the message when the message state = 0 it mean not read

example of data in table

roger 0
paul 1
roger 0
paul 0
mike 0
mike 0
mike 0

then the result i want is 

roger 2
paul 1
mike 3

any help will be apreciated thanks

Upvotes: 0

Views: 44

Answers (1)

peterm
peterm

Reputation: 92785

Are you looking for this?

SELECT member_id, COUNT(*) AS count
  FROM messages
 WHERE state = 0
 GROUP BY member_id

Here is a dbfiddle demo

I need also to select all field from another table that will match the member_id is that possible in 1 query

Sure, JOIN away

SELECT m.*, t.*
  FROM (
    SELECT member_id, COUNT(*) AS count
      FROM messages
     WHERE state = 0
     GROUP BY member_id
) m JOIN other_table t
    ON m.member_id = t.member_id

Upvotes: 2

Related Questions