CODEWITHSUNDEEP
mysqlsql
AnalystByNature
AnalystByNature

Reputation: 1

How can I show a count value in SQL instead of listing out many rows in the same table?

How can I show a count value in SQL dependent on the value of a field. If I have a table called Orders that has a CustomerID:

CustID
Cust1
Cust2
Cust3
Cust1

How can I have it display repetition count:

CustID  RepCount
Cust1     2
Cust2     1
Cust3     1
Cust1     2

Upvotes: 0

Views: 100

Answers (2)

xQbert
xQbert

Reputation: 35323

First get a count grouped by custID in a sub query then join back.. If window sets worked in mysql, over partition by custID would be a better approach avoiding a subquery, as it doesn't... a subquery is needed.

SELECT O.custID, B.RepCount 
FROM Orders O
INNER JOIN (SELECT count(*) RepCount, CustID 
            FROM Orders 
            GROUP BY CustID) B
  ON B.CustId = O.custID

This approach is needed if you really want to see the same cust# listed multiple times. If not then a simple select count and group by would work. (as others have illustrated)

Upvotes: 1

Ajay
Ajay

Reputation: 554

SELECT CustID, COUNT(CustID) as 'RepCount'
FROM Table1
GROUP BY CustID

You can also have result by this small query

Upvotes: 2

Related Questions