What sql formula am I missing?

Question

I need to find out the busiest location by check in for a query.

select name
from checkin join location on checkin.locid = location.LocID
order by name

This query gives me the result but I cannot figure how to group it by numbers. If I put count (distinct name) it gives a weird result and if I group by name it does not give me the numbers

I am trying to group the names and the amount of times it has come in two separate columns?

Will you explain more details exactly what you want to do. and table schema. — Bheemsen Singh, Aug 17 '20 at 06:42

score 2 · Answer 1 · edited Aug 17 '20 at 06:40

2

Try:

select name , count(checkinpk)
.....
group by name , count(checkinpk)

edited Aug 17 '20 at 06:40

tadman

208,517
23
234
262

answered Aug 17 '20 at 06:38

K Ashok Kumar

21
3

1

Thank you very much. It is exactly what I am looking. – Vij Aug 17 '20 at 06:44
1

aggregate function will not work in group by clause. – Jignesh Bhayani Aug 17 '20 at 06:49
SQl server do support group by aggregate functions https://www.sqlservertutorial.net/sql-server-basics/sql-server-group-by/ – K Ashok Kumar Aug 17 '20 at 18:59

score 0 · Answer 2 · answered Aug 17 '20 at 06:47

0

You can try below:

select name, COUNT(C.LocID) as Checkin_Count
from location AS L
inner join checkin as C on C.LocID= L.LocID
group by name
order by COUNT(C.LocID) desc

answered Aug 17 '20 at 06:47

Jignesh Bhayani

83
3

What sql formula am I missing?

2 Answers2