[SOLVED] Union with count - return multiple fields

[jnmunsey]

This is my query:

 

(
SELECT `unixtime2` , `pagenum` , count( DISTINCT `ipaddress` ) AS pagenumtotal
FROM `orders1`
WHERE unixtime2 >20081031
AND pagenum != ''
GROUP BY `unixtime2` , `pagenum`
ORDER BY `unixtime2` , pagenum ASC
)
UNION (

SELECT `unixtime2` , `pagenum` , count( DISTINCT `ipaddress` ) AS pagenumtotal
FROM `ordersbp`
WHERE unixtime2 >20081031
AND pagenum != ''
GROUP BY `unixtime2` , `pagenum`
ORDER BY `unixtime2` , pagenum ASC
) 

 

This query yields two separate values for each date and doesn't merge them, a result of the "Count" function.  If I were doing a query for just the count alone could use  "+" instead of "union" but I need to return values based on the date, pagenum and the unique count of ip addresses for each.

 

Advice? Thanks so much!

[veridicus]

Try a subquery:

 

SELECT unixtime2, pagenum, count(DISTINCT ipaddress) as pagenumtotal
FROM 
(
SELECT `unixtime2` , `pagenum` , `ipaddress`
FROM `orders1`
WHERE unixtime2 >20081031
AND pagenum != ''
GROUP BY `unixtime2` , `pagenum`
ORDER BY `unixtime2` , pagenum ASC
)
UNION ALL (

SELECT `unixtime2` , `pagenum` , `ipaddress`
FROM `ordersbp`
WHERE unixtime2 >20081031
AND pagenum != ''
GROUP BY `unixtime2` , `pagenum`
ORDER BY `unixtime2` , pagenum ASC
)) a 

 

(untested)

[veridicus]

Forgot to remove the GROUP BY and ORDER BY from each subquery and move them into the outer query, but you get the idea.

[jnmunsey]

Thanks got it to work.  Looks like this:

 

SELECT unixtime2, pagenum, count( DISTINCT ipaddress ) AS pagenumtotal
FROM (
(

SELECT `unixtime2` , `pagenum` , `ipaddress`
FROM `orders1`
WHERE unixtime2 > $numweeks
AND pagenum != ''
)
UNION ALL (

SELECT `unixtime2` , `pagenum` , `ipaddress`
FROM `ordersbp`
WHERE unixtime2 > $numweeks
AND pagenum != ''
)
) AS ABC
GROUP BY `unixtime2` , `pagenum`
ORDER BY `unixtime2` , pagenum ASC