CODEWITHSUNDEEP
mysqlsqlcount
Pavel Straka
Pavel Straka

Reputation: 427

SQL - Get all rows of table one and count of rows

How do I create an SQL query which gets all the rows of the table and count of rows inserted under e-mail?

I tried something like this, but this groups the rows and so I don't get all the rows.

SELECT *, COUNT(email) AS 'count' FROM adverts GROUP BY email

Upvotes: 0

Views: 63

Answers (2)

user2221088
user2221088

Reputation:

try this,

select *,
    (select count(Email) 
    from adverts  where adverts.Email =a.Email) as EmailCount
    from adverts as a

or this

SELECT *, COUNT(email) OVER (PARTITION BY  email) as EmailCount FROM adverts

Upvotes: 1

juergen d
juergen d

Reputation: 204746

select a1.*, a2.count
from adverts a1
join
(
  SELECT email, COUNT(*) AS 'count' 
  FROM adverts 
  GROUP BY email
) a2 on a1.email = a2.email

Upvotes: 2

Related Questions