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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> > 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.

Yes you do have the column, but your first part doesn't tell the query
which table to use.

UNION is used to join the result of queries together. Each of those
queries must be correctly formed.  The first part of your SQL (before
the first UNION) does not contain a FROM clause, so MySQL tells you
there is no column called 'solarLandingDateTime' because you have no
table.  This same problem applies to each part of your unions.

> ------------
> John Butler (Govinda)
> govinda.webdnatalk@xxxxxxxxx
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

--
Niel Archer
niel.archer (at) blueyonder.co.uk



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux