Counting Two Fields In One Query

[dev-ria]

hello,

i need to count two different fields in one query but I keep getting the count of only the first field FirstExtracted.

 

           SELECT artist,title,url,FirstExtracted,
		COUNT(FirstExtracted) AS FirstCount,
		COUNT(LastExtracted) AS LastCount,
		DATE_FORMAT(`FirstExtracted`,'%m-%d') AS Seconddate
		FROM results
		WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d')
		GROUP BY Seconddate
           ORDER BY DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') DESC
           LIMIT 20

I need results as $run['Seconddate']."', ".$run['FirstCount'].",".$run['LastCount'] not sure what I'm missing here.

[Christian F.]

Tried running that query in the MySQL client or in phpMyAdmin?

[dev-ria]

i ran it on a php page and mysql workbench. they only bring count of FirstExtracted. It makes count of LastExtracted the same as the FirstExtracted.

[Christian F.]

You have some example data we could check with?

[Barand]

When you do field count, as you are, only those containing NULL are excluded. Zeros and blank strings are included. Since you have those two fields in each record it is not surprising the count is the same for both.

[akphidelt2007]

You have to rethink your query to figure out what exactly you are trying to count and 99.99% chance you'll have to use a subquery since grouping and counting will yield you the same results for both counts.

Edited October 23, 2012 by akphidelt2007

[pantu]

Probably like this

SELECT
artist,
title,
url,
FirstExtracted,
   DATE_FORMAT(`FirstExtracted`,'%m-%d') AS Seconddate,
(SELECT COUNT(FirstExtracted) FROM results WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d')) AS FirstCount,
   (SELECT COUNT(LastExtracted) FROM results WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d')) AS LastCount
FROM results
WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d')
GROUP BY Seconddate
ORDER BY DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') DESC
LIMIT 20

 

But why do you do a count in combination with a limit...?

[dev-ria]

thanks for the help guys. pantu the query didnt work as planned. it counted all the records in the field and didnt split them by date. but the query below i put together seems to work except when I call the LastCount in my while() it comes back as Notice: Undefined index: LastCount

 

SELECT LastExtracted,FirstExtracted,
COUNT(FirstExtracted) AS FirstCount,
DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate,
DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate
FROM results
WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d')
UNION ALL
SELECT LastExtracted,FirstExtracted,
COUNT(LastExtracted) AS LastCount,
DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate,
DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate
FROM results
WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d')
GROUP BY Firstdate
ORDER BY Firstdate DESC
LIMIT 20

 

 

the reason for the limit is im creating a graph so i only need the last 20 days.

[dev-ria]

ugh its calling LastCount (added LastCount to first SELECT) but it still counts the same as first FirstCount.

 

          (SELECT LastExtracted,FirstExtracted,
          COUNT(FirstExtracted) AS FirstCount,
          COUNT(LastExtracted) AS LastCount,
          DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate,
          DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate 
          FROM results
          WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d'))
          UNION ALL
          (SELECT LastExtracted,FirstExtracted,
          COUNT(FirstExtracted) AS FirstCount,
          COUNT(LastExtracted) AS LastCount,
          DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate,
          DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate 
          FROM results
          WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') GROUP BY Firstdate) 
          ORDER BY Firstdate DESC
          LIMIT 20

[dev-ria]

here is sample of what the data looks like

Edited October 24, 2012 by dev-ria