Total Records

[jazzy639]

Hello,

I have a database where several users make bookings.

I want to get the total number of bookings made by each user so I can display the users name and the total number of bookings on one pgae.

I am not sure how to do this.

This is what I have got so far but it does not work:

SELECT DISTINCT rlast + ', ' + rfirst AS sname, * FROM booked WHERE sdate >= DATE() AND sdate < DATE()+7 ORDER BY total DESC

Please help,

Thanks

[phpnovice]

I'd have to get answers to a lot more questions, but the following is a general outline:

SELECT DISTINCT rlast + ', ' + rfirst AS sname, COUNT(sname) AS total
FROM booked
WHERE sdate >= DATE()
AND sdate < DATE()+7
ORDER BY total DESC
GROUP BY sname;

[jazzy639]

Hello,

That doesnt work.

In the database, there are multiple users. They could have made only 1 booking or even 9.

I want to take the users lastname and firstanem and combine them (which I have done) and then get the total number of bookings which are in the database with that users name when it is between the current day and a week ahead.

I want this to work for as many different users there are in the database.

Will one recordset do this job?

Thanks

[phpnovice]

The COUNT() function counts the rows in the table for a given user. If there is a numeric booking column in the table, then you use the SUM() function for that. You need the GROUP BY clause to get separate COUNTs and/or SUMs for each DISTINCT user.

[wmif]

you will need to run a subquery to get results like i think i understand you want.

SELECT DISTINCT rlast + ', ' + rfirst AS sname,
(select count(rlast) from booked as booked1 where booked1.rlast = booked.rlast and booked1.rfirst = booked.rfirst and sdate >= DATE() AND sdate < DATE()+7) as usercount
FROM booked
WHERE sdate >= DATE() AND sdate < DATE()+7
ORDER BY total DESC

[phpnovice]

That is a lot of unnecessary extra processing. jazzy639, if you supply me with a copy of your database, I'll give you a single select statement, modelled after the one I've already given you, that will produce the desired results with a lot less overhead.

[jazzy639]

Hello,

Using phpnovie's query, it works but I can't select any other field to use, I changed it to the following to get it to work:

SELECT teachersname, COUNT (teachersname) as stotal
FROM booked
WHERE sdate >= date() AND sdate < date()+7 GROUP BY teachersname
ORDER BY COUNT(teachersname) DESC

Wmif's doesn't work at all. I get "ORDER BY... conflicts with distinct". When I took out the distinct part, I get "No value given for one of more required parameters"

I cannot give you the database as this is a live system at the moment, this is an improvement to it so you can see how many bookings each person has made.

Here are the field names for the table "Booked":

bookedid
sdate
sday
period
room
rfirst
rlast
username
email

The fields I need are the bookedid (ID), rlast and rfirst combined, username, email, and not forgetting the total number of bookings in the current week.

Later, I will make the dates selectable for the amount of bookings.

Thanks for your help so far

[wmif]

for my query change the order by. i didnt catch that when i pasted that in.

ORDER BY total DESC
-to-
ORDER BY usercount DESC

[jazzy639]

Hello wmif, I get the exact same errors with the DISTINCT and then no value given.

I posted one that does work. Is it possible to integrate the extra fields into that?

Thanks

[phpnovice]

Using phpnovie's query, it works but I can't select any other field to use...
What do you mean? What error message do you get?

SELECT COUNT(teachersname) as stotal, *
FROM booked
WHERE sdate >= date() AND sdate < date()+7
ORDER BY 1 DESC, teachersname ASC
GROUP BY teachersname

[jazzy639]

Hello,

When I did your query, phpnovie, I get " cannot group on fields selected with "*" "

If I take out the *, I get no value given for one of more required parameters.
If I take out teachersname and change GROUP by above ORDER BY and take out ORDER BY 1, it works but it doesnt show me the username.

So far, the best is:

SELECT teachersname, COUNT (teachersname) as stotal
FROM booked
WHERE sdate >= date() AND sdate < date()+7 GROUP BY teachersname
ORDER BY COUNT(teachersname) DESC

Any other ways of displaying other fields?

Thanks

[phpnovice]

Did you try:

SELECT teachersname, COUNT(teachersname) as stotal, *
FROM booked
WHERE sdate >= date() AND sdate < date()+7
ORDER BY 2 DESC, 1 ASC
GROUP BY teachersname

[jazzy639]

Hello,

When I do that, I get "Sytax error in order by clause"

Doesn't the GROUP BY have to come first?

What is the 2 and 1 for?

Thanks for your help so far

[phpnovice]

Well, if it has to come first, then try this:

SELECT teachersname, COUNT(teachersname) as stotal, *
FROM booked
WHERE sdate >= date() AND sdate < date()+7
GROUP BY teachersname
ORDER BY 2 DESC, 1 ASC

Otherwise, the ORDER BY clause can use the ordinal position numbers of the selected columns -- instead of the names of the columns.

[jazzy639]

Hello,

When I do that query I get

Cannot group fields selected with "*"

Any other ideas?

Thanks

[jazzy639]

Hello,

I sorted it with

SELECT teachersname, COUNT(teachersname) as stotal, email, rlast, rfirst FROM booked WHERE sdate >= date() AND sdate < date()+7 GROUP BY teachersname, email, rlast, rfirst ORDER BY 2 DESC, 1 ASC

That works but I still cannot do rlast + ', ' + rfirst AS sname, which is why I have done them separetely.

Thanks everyone especially phpnovice for pointing me in the right direction

[phpnovice]

Instead of the asterisk, just list each column you need.

[jazzy639]

Yes, that works but I cannot add fields together like:

rlast + ', ' + rfirst AS sname

It works in all my other statements. It says that it is not part of an aggregate function or something like that.

[phpnovice]

Hmmm... Did you try something similar to the following?

SELECT COUNT(teachersname) as stotal, teachersname, (rlast & ', ' & rfirst) As fname, email
FROM booked
WHERE sdate >= date()
AND sdate < date()+7
GROUP BY teachersname, fname, email
ORDER BY 1 DESC, 2 ASC

[jazzy639]

I tried

SELECT COUNT(teachersname) as stotal, teachersname, rlast + ', ' + rfirst AS sname, email
FROM booked
WHERE sdate >= date()
AND sdate < date()+7
GROUP BY teachersname, sname, email
ORDER BY 1 DESC, 2 ASC

Isn't that the same thing?

Thanks

[phpnovice]

Maybe ... but with what result? Same error message?