CODEWITHSUNDEEP
mysqlselectcount
Guybrush
Guybrush

Reputation: 1611

MySQL - Show Occurrences (Count) in a SELECT

Here is my SQL:

SELECT
  orders.ord_Id,
  orders.ord_Email,
  products.pdt_Name
FROM
  orders, products
WHERE
  orders.ord_ProdId = products.pdt_Id
ORDER BY
 ord_Id;

And here is a sample result:

ord_Id  ord_Email          pdt_Name
1       [email protected]    Product1
2       [email protected]    Product2
3       [email protected]    Product2
4       [email protected]    Product1
5       [email protected]    Product2
6       [email protected]    Product2

I need to add an extra column that shows the occurrences of same e-mails in the list. Something like this:

ord_Id  ord_Email        Count  pdt_Name
1       [email protected]  3      Product1
2       [email protected]  3      Product2
3       [email protected]  2      Product2
4       [email protected]  3      Product1
5       [email protected]  2      Product2
6       [email protected]  1      Product2

I already tryied using COUNT(DISTINCT(ord_Email)) and insert a SELECT inside this SELECT, but had no sucess.

PS. I need the entire list appears in the result.

Please, can you help me? Thanks!

Upvotes: 0

Views: 51

Answers (2)

Quassnoi
Quassnoi

Reputation: 425713

SELECT  or.ord_email,
        o.ord_id,
        cnt,
        p.pdt_name
FROM    (
        SELECT  ord_email, COUNT(*) cnt
        FROM    orders
        GROUP BY
                ord_email
        ) oe
JOIN    orders o
ON      o.ord_email = oe.ord_email
JOIN    products p
ON      p.pdt_id = o.ord_prodid

Create an index on orders (ord_email) for this to work fast.

Upvotes: 1

user3741598
user3741598

Reputation: 295

Please read up on GROUP BY (in your case ord_Email) - that is the way to combine - then the COUNT will yield the occurences you're after. Good luck.

Upvotes: 0

Related Questions