Trouble with count

trygve

I have a query that is working fine.

$sql = "Select tech, count (*) as tickets from $table where status = 'open' group by tech";

which gives a listing of the numer of open tickets by tech. Now I also need to show (on the same screen) how many of those open tickets are 10 days old or greater by tech. I do have a column in the table with the ticket open date (odate).

Keep get parse errors. Any ideas on how to do this without changing the where clause?

leatherback

Hi,

Have you tried removing the space between count and ()?
Also, I am not sure wether count() should work. If you still have trouble try count(tech)

Hope this helps,

trygve

Thanks for the reply, let me clarify.

The query listed is counting the total number of tickets and is working (I may have an extra space when I retyped it here in the forum). As an example I see that tech #1 has 20 open tickets.

What I need is how to additionally count the rows where the date in the row (odate) is 10 days or older. So for example I would see for tech #1 that they have 20 tickets 3 of which are more than 10 days old.

Sorry for the confusion.

tomhath

You can do it with a case statement. Here's the rough idea. Replace "--older than ten days--" with the condition you need to test (presumably some kind of unixtimestamp difference between the current time and odate).

Select tech, 
count (*) as tickets 
sum (case when --older than ten days-- then 1 else 0 end) as oldtickets
from $table 
where status = 'open' 
group by tech

trygve

That did it!

Thanks