Restructuring query.

jmut · #1 (**permalink**) 12-23-05, 03:16

Hi,
I got a little trouble with my query. It was intendet to do one thing but now the data I should fetch is changed so I am thinking could I rewrite the query to fix the problem or should I use multiple queries and php after that.

MySQL 4.1

Code:

SELECT DAYNAME( date ) AS pd, count( * ) AS amount, produkt_id
                        FROM contract
                        WHERE ( 1 )
                            AND contract_status_id = 1
                            AND WEEK( date, 1 ) = 51
                            AND YEAR( date ) = 2005
                        GROUP BY produkt_id, pd
                        WITH ROLLUP

With this query I get data similar to this:

Code:

pd      amount   produkt_id
Monday 	2 	50037
Saturday 	1 	50037
Wednesday 	1 	50037
NULL 	4 	50037
Friday 	1 	50091
Monday 	1 	50091
Wednesday 	1 	50091
NULL 	3 	50091
Monday 	2 	50199
Tuesday 	1 	50199

This works perfectly if I am looking for data for the current week only.
But now I have to search for all data till that week.
So for example one may mistakanly do :

Code:

SELECT DAYNAME( date ) AS pd, count( * ) AS amount, produkt_id
                        FROM contract
                        WHERE ( 1 )
                            AND contract_status_id = 1
                            AND WEEK( date, 1 ) <= 51
                            AND YEAR( date ) <= 2005
                        GROUP BY produkt_id, pd
                        WITH ROLLUP

But this will gather data for all mondays,tuesdays etc etc. not getting it per day for all products of one type.

(and will work for current year only).

I hope I did explain the problem.
Thanks in advance for spending time.
Cheers,
JD

Internet Services

Web Development

Digital Marketing

Consumer Tech