PHP SQL - number of entries in a date column

Author

number of entries in a date column

bigsamurai

2005-12-02, 9:05 pm

I have a field in a table called ood_date. I want to run a query that
will tell me the number of entries between 2 given dates. the end
result would be...

officer name number of entries number of hours

I have the name and the time issue resolved but i can not egt the query
to either understand the count(ood_date) ot the tot(ood_date) in the
select statement.

Thoughts?

Bob Stearns

2005-12-02, 9:05 pm

bigsamurai wrote:
> I have a field in a table called ood_date. I want to run a query that
> will tell me the number of entries between 2 given dates. the end
> result would be...
>
> officer name number of entries number of hours
>
>
> I have the name and the time issue resolved but i can not egt the query
> to either understand the count(ood_date) ot the tot(ood_date) in the
> select statement.
>
> Thoughts?
>
SELECT "officer name", count(ood_date) as N_o_E, sum(hours) as N_o_H
FROM <table_name>
GROUP BY "officer name"
WHERE ood_date BETWEEN $d1 AND $d2
ORDER BY "officer name"

is close, and the best I can do given the lack of both DDL and sample data

bigsamurai

2005-12-02, 9:05 pm

Thanks. I have included the actual query I was running below.

SELECT ood.ood_watch_officer, ood.ood_start, ood.ood_end, ood.ood_date,
COUNT(ood.ood_date), ood_per.ood_per_no, ood_per.ood_per_name FROM ood,
ood_per
WHERE ood.ood_watch_officer=ood_per.ood_per_no
AND (ood.ood_date BETWEEN '$_POST[date1]' AND
'$_POST[date2]')
ORDER by ood_per.ood_per_name";

Two tables ood and ood_per

Bob Stearns

2005-12-02, 9:05 pm

bigsamurai wrote:
> Thanks. I have included the actual query I was running below.
>
> SELECT ood.ood_watch_officer, ood.ood_start, ood.ood_end, ood.ood_date,
> COUNT(ood.ood_date), ood_per.ood_per_no, ood_per.ood_per_name FROM ood,
> ood_per
> WHERE ood.ood_watch_officer=ood_per.ood_per_no
> AND (ood.ood_date BETWEEN '$_POST[date1]' AND
> '$_POST[date2]')
> ORDER by ood_per.ood_per_name";
>
> Two tables ood and ood_per
>
More human readably below. Without a GROUP BY, you are asking for every
every row, so what is COUNT supposed to produce? I believe, in this
case, that it returns 1 every time. If you are trying to produce
intermediate summary lines in a longer report, you need to look into
doing that with your php code or a much fancier bit of sql involving
UNIONs of separate SELECTS for your detail lines and your summary lines.
DB2 has some capabilities in this area which I do not know (yet) how to
use: see ROLLUP and CUBE under GROUP BY. Perhaps your RDMS has some
similar extensions.

SELECT ood.ood_watch_officer, ood.ood_start,
ood.ood_end, ood.ood_date,
COUNT(ood.ood_date), ood_per.ood_per_no,
ood_per.ood_per_name
FROM ood, ood_per
WHERE ood.ood_watch_officer=ood_per.ood_per_no
AND (ood.ood_date BETWEEN '$_POST[date1]' AND
'$_POST[date2]')
ORDER by ood_per.ood_per_name";

bigsamurai

2005-12-05, 6:59 pm

i am trying to determine the number of entrys by this person between
these dates.

Rich

2005-12-06, 7:01 pm

In article <1133800584.935958.48240@f14g2000cwb.googlegroups.com>, bigsamurai
says...
>
>i am trying to determine the number of entrys by this person between
>these dates.
>

As Bob mentioned you need to use the "group by" with the "count" to get a
summary for each person. The "<labels>" are generic names used below that would
have to be replaced with your information. Borrowing from Bob, maybe something
like...

Select count(*) as entries, <officer_name>
FROM <table_name>
GROUP BY ood_date, <officer_name>
WHERE ood_date >= '<low_date>' AND ood_date <= '<high_date>';

Maybe the problem is related to trying to include the ood_date in the select. If
you just need the count without the specific date information you might be able
to simplify your query.

Rich
--
Newsguy -- http://newsguy.com

Sponsored Links