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?
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)
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