Query Question

[Erdenemandal]

I have a table with Br_Date timestamp. and Now I want to make report for September . (for example)

 

Day | Number of entries

2004-09-01 | 6

2004-09-02 | 2

 

and I use for this report 2 queries

 

$myDate = "select distinct substr(br_date,0,11) as myDate from StimulanzResult WHERE br_DATE LIKE '%%%%-09-%%'"

select count(*) from StimulanzResult WHERE substr(br_date,0,11) = $myDate

 

 

Can I use just 1 query instead 2 query.

 

Thanks,

Erdenemandal

[kejpa]

It somewhat depends on your database engine, but something like this ought to get you going....

 

Select "Sept", Sum(Entried) from StimulanzResult group by BR_date having Br_date like "%%%%-09-%%"

 

 

HTH

Kejpa

[Erdenemandal]

Can you explain more details. I am using postgresql.

 

what is Entried ?

[kejpa]

oki,

Entried should have been Entries, ie your field "Number of entries"

 

I have no experience with PostgreSQL, but you need to make an Group by clause where you group according to which month and then you do a sum for the number of entries.

Have a look at aggregate functions in the help files for Postgree, I'm sure it's in there.

 

HTH

Kejpa

[Erdenemandal]

Ok, I will try it.

 

Thanks,

[Nerseus]

I'm not sure if your database supports it, but you can usually do a "sub select" to combine your queries:

select count(*) from StimulanzResult 
WHERE substr(br_date,0,11) = 
(
   select distinct substr(br_date,0,11) as myDate from StimulanzResult WHERE br_DATE LIKE '%%%%-09-%%'
)

 

If your database supports it, I'd suggest using a MONTH or DATEPART function (those both exist in SQL Server, not sure about your database). For example:

SELECT count(*) 
FROM StimulanzResult 
WHERE MONTH(br_date) = 9

OR

SELECT count(*) 
FROM StimulanzResult 
WHERE DATEPART(month, br_date) = 9

 

-ner