mysql COUNT with UNION php

Go To StackoverFlow.com

2

i am having a problem adding COUNT to my query.
the query works fine but as soon as i add COUNT(*) AS totalNum
i get 1 result from each table

$query = "(SELECT 'table1' AS tablename, navid, thumb, title, longText, clicks AS allClicks, COUNT(*) AS totalNum
FROM table1 
WHERE $column=1 
AND enabled=1)

UNION DISTINCT

(SELECT 'table2' AS tablename, navid, thumb, title, longText, clicks AS allClicks, COUNT(*) AS totalNum 
FROM table2
WHERE $column=1 
AND enabled=1) 
ORDER BY allClicks DESC";


while ($row = mysql_fetch_assoc($result)){
    $navid = $row['navid'];
    $thumb = $row['thumb'];
    $tablename = $row['tablename'];
    $title = strtoupper($row['title']);

    etc...

}

question: what is the best way to add count(*) into my my join query?

2012-04-05 16:18
by t q


1

When using an aggregate function, such as COUNT, you need to include a GROUP BY clause:

(SELECT 
    'table1' AS tablename, 
    navid, 
    thumb, 
    title, 
    longText, 
    clicks AS allClicks, 
    COUNT(*) AS totalNum
FROM table1 
WHERE 
    $column=1 
    AND enabled=1
GROUP BY navid, thumb, title, longText, clicks)

UNION DISTINCT

(SELECT 
    'table2' AS tablename, 
    navid, 
    thumb, 
    title, 
    longText, 
    clicks AS allClicks, 
    COUNT(*) AS totalNum 
FROM table2
WHERE 
    $column=1 
    AND enabled=1
GROUP BY navid, thumb, title, longText, clicks) 
2012-04-05 16:19
by Michael Fredrickson
if i wanted to get the COUNT, would this do it in the while loop? $totalNum = $row['totalNum'] - t q 2012-04-05 16:27
I've never written a single line of PHP... but yea, that looks like it should work - Michael Fredrickson 2012-04-05 16:34
i keep getting a count result of - t q 2012-04-05 16:41
COUNT tells you how many rows in your result have the same values for navid, thumb, title, longText, clicks. What are you wanting to get a count of? The total number of rows in your result - Michael Fredrickson 2012-04-05 16:44
Ads