SELECT COUNT always gives value 1

[gavray]

Hi can u help.

I've got the code below which gives me the value 1 for all cases when they range from 1 to 10, in many instances.

sql2 = "SELECT ID,SongView,Song,Count(ID) AS num FROM SongTable"
sql2=sql2 & " WHERE myID = "&myID
sql2=sql2 & " AND LiveSong=1"
sql2=sql2 & " Group by SongView,Song"

numberofsongs=rs("num&quot

Any ideas please,

Many Thanks

Gavin

the boho from soho

 

[PraveenMenon]

gavray,

i feel this is because, u are grouping it by SongView and Song. These fields could be the primary key in this table, and may be are not duplicated.

 

[r937]

if you want the total number of songs along with details for each song, there's a property of the result set (recordcount? sorry, i don't know what it's called in asp) that you can simply print

for other aggregates, like summing up a column, you can get that simply by doing the aggregate yourself in the output language

or, you can get aggregates with a separate query that you run along with the detail query in a union

here's what it would look like to get your count

select ID
     , SongView
     , Song
     , 0           as num
  from SongTable
 where myID = n
   and LiveSong=1
union all
select 0
     , null
     , null
     , count(ID)   as num
  from SongTable
order
    by SongView
     , Song

like i said, this is probably not necessary if all you want is the number of records in the result set

rudy

 

[miq]

for a count to show greater then 1 group by column should have duplicate values. Say, if there ia a number which exists 5 times then the count will show 5.


Bye
miq