MYSQL MAX/COUNT/IN/DATE

Question

Hi All

Hope you can advise. Im pulling my hair out on a query and hope you can advise.

I have a table where I want to select MAX from an inner count between dates with an IN.

View Table

Here is what I have tried but with no luck at all.

SELECT MAX(no_hits) from (SELECT count(hits) AS 'no_hits' ) FROM stats WHERE 'date' >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH AND zones_code IN('011242077793513596890', '011242077783866125432'))

So basically I only want one no_hits returned for the best performing zone.

Hope you can advise on where im going wrong.

And thank you if you cam

Answer 1

SELECT `zones_code`, SUM(`hits`) AS `no_hits`
FROM `stats`
WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY `zones_code`
ORDER BY `no_hits` DESC
LIMIT 1

Answer 2

I would use a ORDER BY DESC ordering clause with a LIMIT 1 to get only the maximum value.

Answer 3

Your query is this:

SELECT MAX(no_hits) 
FROM (SELECT count(hits) AS 'no_hits' ) FROM stats 
  WHERE 'date' >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH 
    AND zones_code IN('011242077793513596890', '011242077783866125432')
);

Here are some things wrong with your query:

• You closed the subquery parentheses after 'no_hits'.
• You didn't supply a table alias for the subquery. Every derived table must have a table alias.
• You didn't close the parentheses for the DATE_SUB() function.
• You used COUNT() where I think you should use SUM(), if you want the total of hits per zone.
• You aren't associating the subtotal of hits with each zone; your subtotal is for the whole table.
• You used string delimiters ('') for 'date' instead of identifier delimiters (i.e. back-quotes). You're comparing the literal string 'date' to a date value, when you mean to compare the column date to a date value.

The query in @chaos's answer is close, but I think you should use SUM():

SELECT `zones_code`, SUM(`hits`) AS `no_hits`
FROM `stats`
WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY `zones_code`
ORDER BY `no_hits` DESC
LIMIT 1;

The result is zone_code 011242077793513596890, with a total of 255 hits.

---

PS: When you ask questions online, please supply in textual format enough code and data for people to test easily. You supplied a screenshot of some sample data, and without showing the table creation code. This is not as helpful as if you had supplied a valid CREATE TABLE statement and a sample INSERT statement to populate it.

CREATE TABLE IF NOT EXISTS `stats` (
  `id` int(11) NOT NULL,
  `zones_code` char(21) default NULL,
  `date` date default NULL,
  `hits` int(11) default NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO stats VALUES
(10, '011242077793513596890', '2009-05-11', 13),
(12, '011242077793513596890', '2009-05-12',235),
(24, '011242077793513596890', '2009-05-13',  2),
(32, '011242077793513596890', '2009-05-14',  5),
(17, '011242077783866125432', '2009-05-12',165),
(22, '011242077783866125432', '2009-05-13',  2),
(30, '011242077783866125432', '2009-05-14',  5),
(19, '011242077743853330663', '2009-05-12', 61),
(20, '011242077737314753388', '2009-05-12', 54),
(28, '011242077737314753388', '2009-05-13',  7),
(36, '011242077737314753388', '2009-05-14', 31),
(14, '011242077730456603312', '2009-05-12',240),
(26, '011242077730456603312', '2009-05-13',  2),
(34, '011242077730456603312', '2009-05-14',  5);

The above is what I had to type in based on your screen shot!

Do yourself a favor and make it easier for people to help you.