Select Count Group By 1

[A1Pat]

Hi all,

I'm trying to get data from my database with this code

mySQL = "SELECT Count(*) AS total_count FROM tblTechSupports " _
				&" WHERE DateTime = '" & get_day & "' " _
				&" AND charged = 1"

It works fine until I want to add another field into this code like:

mySQL = "SELECT Count(*),ID AS total_count FROM tblTechSupports " _
				&" WHERE DateTime = '" & get_day & "' " _
				&" AND charged = 1"

then, all bets are off.

An error keeps telling me to use GROUP BY or something likes that which I have very little understand about. Hope you can tell me how!!!

Thanks!

 

[ptheriault]

Whenever you use aggregates (COUNT, SUM, MIN, MAX) and select more than one column you have to group the data. So add
GROUP BY ID at the end of your query.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[A1Pat]

Is this how it should look like:

"SELECT Count(*) AS total_count, ID FROM tblTechSupports " _
				&" WHERE DateTime = '" & get_day & "' " _
				&" AND charged = 1 GROUP BY ID"

I didn't get the error anymore, neither the data display.
by the way, this is how I want to display the ID data:

do while not rs.eof
  response.write rs("ID") & ","
rs.movenext
loop

 

[A1Pat]

Nevermind Paul,

I bad when I called the ID wrong (typo).

Thank you for the help.

By the way, what if I want to call more than just ID field. how about 3 or more fields. Do I need to use GROUP BY for all of them?

 

[ptheriault]

Yes,
But be carefull because it can change your result set as you add more columns.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[A1Pat]

Great... thanks alot!!!

keep up the good work!