Question 20714 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL get count per repeated value in field – Mysql

I have a table similar to the following. (The addresses are actually IP addresses, but I’m keeping them short for simplicity.)

| Cust ID | Address |
| ------- | --------|
|    1    | 111     |
|    1    | 222     |
|    1    | 222     |
|    1    | 222     |
|    1    | 333     | 
|    2    | 444     | 
|    2    | 444     | 
|    2    | 444     |
|    2    | 444     |
|    3    | 555     |

I would like to get the count of the different addresses for each customer, sorted by the counts:

| Cust ID | Address | Count |
| ------- | --------|-------|
|    2    | 444     |   4   |
|    1    | 222     |   3   |
|    1    | 111     |   1   |
|    1    | 333     |   1   |
|    3    | 555     |   1   |

So, per above, customer 2 used the address 444 four times; customer 1 used the address 222 three times, 111 one time, and 333 one time; etc.

FWIW, I’m using mysql.

Thanks!

Tags:

3

Answers


  1. 0 
    SELECT   CustID, Address, COUNT(*)
FROM     Cust
GROUP BY CustID, Address
ORDER BY COUNT(*) DESC;

    A good rule of thumb when using GROUP BY is to pass it all selected columns not used by COUNT.

    Login or Signup to reply.
  2. 0

    select CustId,
    Address,
    count(Address) as Count

    from Cust

    Group by CustId, Address;

    Login or Signup to reply.
  3. 0

    To aggregate the frequency of addresses among the different customer IDs, a GROUP BY clause is used in conjunction with a COUNT clause to determine the frequency of addresses for each customer ID.

    SELECT Cust ID, Address, COUNT(*) FROM TABLE GROUP BY Cust ID, Address ORDER BY COUNT(*) DESC;

    Please note that for the GROUP BY clause, both Cust ID and Address are being grouped. COUNT(*) is being used to count all entries that appear within each group.

    As another example, if one only wished to determine the frequency of addresses while disregarding which customer ID they belong to – one would only group by Address:

    SELECT Address, COUNT(*) FROM TABLE GROUP BY Address ORDER BY COUNT(*) DESC;

    However, grouping by multiple columns (as in the first case) narrows down the results to ensure that aggregates across both of the relevant columns are considered.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search