How to select field base on date

[fauzey]

Hi appreciate if someone there can help me on this...

SELECT date AS today_date,date + INTERVAL -1 DAY AS yerterday_date, SUM(manual_east) + SUM(manual_west) + SUM(tng_east) + SUM(tng_west) + SUM(stag_east) + SUM(stag_west) AS today_traffic,SUM(manual_east) + SUM(manual_west) + SUM(tng_east) + SUM(tng_west) + SUM(stag_east) + SUM(stag_west) AS yesterday_traffic FROM dk WHERE yesterday_traffic = yerterday_date GROUP BY DATE

when run the query above error #1054 - Unknown column 'yesterday_traffic' in 'where clause'. The question is how to select yesterday_traffic based on yerterday_date.

Thanks

[enigma1]

You cannot use an alias with the where clause. But you can use with having.

....FROM dk GROUP BY DATE having yesterday_traffic = yerterday_date....

[fauzey]

Hi Enigma1.. thanks for reply my message. Actually, I want yesterday_traffic value for 2010-10-11 are 155704 and value for 2010-10-10 will get from previous day value. But query below will get both today&yesterday traffic value are same. Can you guide me how to do it.

SELECT date, SUM( manual_east ) + SUM( manual_west ) + SUM( tng_east ) + SUM( tng_west ) + SUM( stag_east ) + SUM( stag_west ) AS today_traffic, date AS today_date, SUM( manual_east ) + SUM( manual_west ) + SUM( tng_east ) + SUM( tng_west ) + SUM( stag_east ) + SUM( stag_west ) AS yesterday_traffic, date + INTERVAL -1 DAY AS yesterday_date FROM dk WHERE DATE BETWEEN '2010-10-10' AND '2010-10-12' GROUP BY date

output query:

date today_traffic today_date yesterday_traffic yesterday_date
2010-10-10 155704 2010-10-10 155704 2010-10-09
2010-10-11 128744 2010-10-11 128744 2010-10-10

[enigma1]

Yes because the sum affects all the filtered rows so each row returns the same value for both the today and yesterday data.

You need to use the same table with 2 aliases if you want to retrieve one row with both today and yesterday data.

something like:
SELECT tb1.date, SUM( tb1.manual_east )...AS today_traffic...SUM( tb2.manual_east )....AS yesterday_traffic....from dk tb1, dk tb2 where tb1.date......and tb2.date...

[fauzey]

Hi enigma1... Problem with adding date into yesterday date... Can you help me on this.. query return NULL

SELECT t1.date, SUM(t1.manual_east) + SUM(t1.manual_west) AS today_manual, t2.date, SUM(t2.manual_east) + SUM(t2.manual_west) AS yesterday_manual, ('today_manual') - ('yesterday_manual') / ('yesterday_manual') * 100 AS value FROM dk t1, dk t2 WHERE 't1.date' = DATE(DATE_ADD(t1.date, INTERVAL - 1 DAY))

query:
date today_manual date yesterday_manual value
NULL NULL NULL NULL NULL

[enigma1]

why do you put regular quotes around the columns?

WHERE t1.date = DATE(DATE_ADD(t1.date, INTERVAL - 1 DAY))
Unless you want specify a string which I doubt.

And you cannot select aliases. You need to do the calculations in each case, so get rid of the quotes.