Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

On Aug 4, 2009, at 6:21 PM, Chris <dmagick@gmail.com> wrote:

Govinda wrote:

I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with:

count | date       | table_name

-------------------------------

5     | 2009-01-01 | table 1
10    | 2009-01-01 | table 2

Ben, Chris, This is as far as I could get so far: $query = "SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing UNION ALL SELECT count(*) AS confirm_count, date(solarAweberConfDateTime) AS solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS aweber_7solar_confirm UNION ALL SELECT count(*) AS thankyou_count, date(solarAWDateTime) AS solarAWDate, 'aweber_7solar_awALIAS' AS aweber_7solar_aw"; $foundUniqueDateROWS = mysql_query($query) or die("query failed: " .mysql_error()); giving this error: query failed: Unknown column 'solarLandingDateTime' in 'field list' but I most certainly do have a column named 'solarLandingDateTime' in the table named 't7solar_landing'. So I am not sure what it's unhappy.

With a union the columns have to have the same name (or alias). Eg: SELECT COUNT(*) AS count, date(solarLandingDateTime) AS unique_date, 't7solar_landingALIAS' AS origin FROM tablename UNION ALL SELECT count(*) AS count, date(solarAweberConfDateTime) AS unique_date, 'aweber_7solar_confirmALIAS' AS origin FROM tablename etc --Postgresql & php tutorials http://www.designmagick.com/ --PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

Correction the column names will come form the first query, but they need to be the same datatype in those columns. So if the first query has a date datatype, the subsequent queries must have date columns in the first position Bastien Sent from my iPod