SQLTeam.com | Weblogs | Forums

SQL Group and Count


#1

Hi to all, I have a sample table below:

name loginid
A 0010
C 0012
B 0013
A 0010
C 0012
B 0013

is it possible to query the table and the output would be like one below:
name totalLogin
A 2
B 2
C 2

any help would be appreciated much .. :slight_smile: thank you!


#2

SELECT name, COUNT(*) AS totalLogin
FROM table_name
GROUP BY name
--ORDER BY name


#3

Hi @ScottPletcher ! this is worth trying! thank you :slight_smile:


#4

Hi @ScottPletcher.. What if i have additional columns in my table? for example name,loginID,loginDate, is it possible to also display the loginDate column?

Thanks!


#5

You could easily display the first and/or the last date (and/or some average of the two). You can show all dates by converting them to strings and concatenating them, but that's a lot of code and overhead.

SELECT name, COUNT(*) AS totalLogin, MIN(loginDate) AS FirstLogin, MAX(loginDate) AS LastLogin
FROM table_name
GROUP BY name
--ORDER BY name


#6

@ScottPletcher What if i want to get the totalLogin in a particular date? is it possible? for example, student A has a totalLogin of 2 on Nov 23 2015// is it possible then?

Thank You so much!


#7

Add a WHERE clause to check for the date.


#8

SELECT name, COUNT(*) AS totalLogin
FROM table_name
WHERE CONVERT(VARCHAR(12), CAST([loginDate] AS DATETIME),112)='20151123'
GROUP BY name

this works fine for me.. but it doesn't display the loginDate column.. How am i supposed to that?


#9

You should be able to figure out from my earlier comments. Good luck with your queries.