CODEWITHSUNDEEP
mysqlsql
Becs Carter
Becs Carter

Reputation: 1250

Select distinct column, and a count of the entries

I have a database table which stores competition entries from users.

I am wanting to select distinct email address, and then the number of entries for each email address. So I want to see how many times each email address has been used for entries.

I am thinking something along the lines of

SELECT DISTINCT `email` FROM `tablename`   but have a count in there somewhere?

Sorry, probably a very basic question really. But I can't seem to get it.

Upvotes: 2

Views: 128

Answers (2)

John Woo
John Woo

Reputation: 263933

Is this what you want?

SELECT email, COUNT(*) totalCount
FROM tableName
GROUP BY email

This will give you unique set of email and give you the total records for the specific email.

COUNT() is an aggregate function which basically count the number of records for each group if GROUP BY is specified, in this case email, but if no GROUP BY then it will count all records in the table.

Upvotes: 3

Kermit
Kermit

Reputation: 34063

CREATE TABLE tbl (`email` varchar(10));

INSERT INTO tbl (`email`)
VALUES
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]');

SELECT email, COUNT(*)
FROM tbl
GROUP BY email;

Result

|   EMAIL | COUNT(*) |
----------------------
| [email protected] |        2 |
| [email protected] |        2 |
| [email protected] |        3 |
| [email protected] |        1 |
| [email protected] |        1 |

See a demo

Upvotes: 1

Related Questions