SQL Count Function By Column

Question

CusID   Order
3001    Hotdog
3001    Sausage
3002    Sausage
3003    Burger
3003    Hotdog
3002    Hotdog
3001    Burger

How I will use this count function?

SELECT COUNT(CusID) AS NumOfOrders FROM Orders

Desired output

CusID   NumOfOrders
3001    3
3002    2
3003    2

Gordon Linoff · Accepted Answer · 2013-07-22 01:46:14Z

6

You need it with a group by:

SELECT CusId, COUNT(CusID) AS NumOfOrders
FROM Orders
group by CusId
order by CusId;

answered Jul 22, 2013 at 1:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Smaug · Accepted Answer · 2013-07-22 03:05:03Z

1

The group by will give you that desired result along with Count function.

select CustID, count(CustID) from Orders
group by CustID

Refer below fiddle link for sample

answered Jul 22, 2013 at 3:05

Smaug

2,6835 gold badges31 silver badges45 bronze badges

Hiren gardhariya · Accepted Answer · 2013-07-22 04:51:19Z

0

select CusID,count(CusID) from Orders group by CusID order by CusId

answered Jul 22, 2013 at 4:51

Hiren gardhariya

1,25512 silver badges29 bronze badges

Novice · Accepted Answer · 2013-07-22 07:34:29Z

0

Try adding GROUP BY and ORDER BY in your query.

SELECT CusId, COUNT(CusID) AS NumOfOrders
FROM Orders
GROUP BY CusId
ORDER BY CusId;

answered Jul 22, 2013 at 7:34

Novice

5582 gold badges9 silver badges21 bronze badges