Topic: Quick SQL question

[Graeme]

I've generated a report in SQL, the user has now asked for a modification (Don't they always!). This is the current query...

SELECT DISTINCT tbluser.fldloginname "Card Number", tbluser.fldfirstname "First Name", tbluser.fldlastname "Surname"
FROM viewreservation join tbluser on tbluser.fldID = viewreservation.flduserID
WHERE viewreservation.fldstarttime > @startdate and fldstarttime < dateadd(day,1,@enddate) and viewreservation.flddeleted = 0
ORDER BY tbluser.fldloginname

In short it is a reservation system. The report generates a list of all users who have used the system each month. The user now wants a column to tell me how many occurrences of each user there are in that month but I'm stuggling. My SQL isn't at an expert level (I'm suprised I got this one working!). I know I need to use the COUNT function but struggling to get it to work.

Any pointers?

[Craig S]

You should just be able to add COUNT(*) to the select.

However, without a GROUP BY I think it will collapse to one result row, even when using DISTINCT.  It's an aggregate function without using a group, so will aggregate everything.

So get rid of the DISTINCT and add
GROUP BY tbluser.fldloginname
before the order clause.

[MindGuerrillas]

Just off the top of my head, I haven't tested this or anything, but try something like

SELECT COUNT(*) "OCCURRENCES", tbluser.fldloginname "Card Number", tbluser.fldfirstname "First Name", tbluser.fldlastname "Surname"
FROM viewreservation join tbluser on tbluser.fldID = viewreservation.flduserID
WHERE viewreservation.fldstarttime > @startdate and fldstarttime < dateadd(day,1,@enddate) and viewreservation.flddeleted = 0
ORDER BY tbluser.fldloginname GROUP BY tbluser.fldloginname

[Graeme]

Thanks both, using a combination of your replies I've managed to get it working

SELECT COUNT(*) "OCCURRENCES", tbluser.fldloginname "Card Number", tbluser.fldfirstname "First Name", tbluser.fldlastname "Surname"

I used that bit from yours.

GROUP BY tbluser.fldloginname
before the order clause.

With this bit from yours, as having the ORDER before GROUP BY spewed an error

Thanks chaps