this:
-----------------------------------------------------
$query = "SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`,
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS
`tableAlias` FROM t7solar_landing GROUP BY DATE(solarLandingDateTime)
UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`,
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY
DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS
`CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`,
'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY
DATE(solarAWDateTime) ";
returns this:
2009-07-28 -------- 5 -------- t7solar_landing
2009-07-29 -------- 1 -------- t7solar_landing
2009-08-02 -------- 2 -------- t7solar_landing
2009-08-03 -------- 3 -------- t7solar_landing
2009-08-04 -------- 2 -------- t7solar_landing
2009-07-28 -------- 2 -------- aweber_7solar_confirm
2009-07-29 -------- 1 -------- aweber_7solar_confirm
2009-07-30 -------- 1 -------- aweber_7solar_confirm
2009-07-31 -------- 1 -------- aweber_7solar_confirm
2009-08-01 -------- 2 -------- aweber_7solar_confirm
2009-08-02 -------- 1 -------- aweber_7solar_confirm
2009-08-03 -------- 2 -------- aweber_7solar_confirm
2009-08-04 -------- 1 -------- aweber_7solar_confirm
2009-07-29 -------- 1 -------- aweber_7solar_aw
2009-07-30 -------- 1 -------- aweber_7solar_aw
2009-07-31 -------- 1 -------- aweber_7solar_aw
2009-08-01 -------- 2 -------- aweber_7solar_aw
2009-08-02 -------- 1 -------- aweber_7solar_aw
2009-08-03 -------- 2 -------- aweber_7solar_aw
2009-08-04 -------- 1 -------- aweber_7solar_aw
Now the first (date) and last (table) column are as expected. But what
happened to count(*)??
There ARE 5 unique dates represented in the first table, as the highest
returned value for count(*) shows, but as you can see, there are 8
unique dates in the second table, and 7 unique dates in the last
table. Why then do we see only "1"s and "2"s in those columns for the
latter 2 tables? And why does NOT the first table's rows include
exclusively ONLY the values "1", "2", "3", "4", "5"? I see two "2"s
and no "4". (??)
Taking this:
SELECT count(*) AS
`CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`,
'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY
DATE(solarAWDateTime)
Your query says:
get the date() from the solarAWDateTime field
then
group by that date
and give me the date and number of results
ie - you asked for the number of results per date - which is what you got.
You didn't ask for the number of unique dates.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
