CODEWITHSUNDEEP
mysqlselectcount
Wellenbrecher
Wellenbrecher

Reputation: 179

A complicated selecting query

SELECT *, IF(start_date < ".$twoDaysAgo.", (posts / 172800 * 50000), (posts / (".$curTime." - start_date) * 50000)) as rating 
FROM 
(
    SELECT t1.*, t2.*, count(t2.id) as posts 
    FROM topics as t1 
    LEFT JOIN 
    (
        SELECT id, topic_id as tid, poster, body, post_date, poster_ip, subject 
        FROM messages t9
    ) as t2 
    ON t1.topic_id = t2.tid 
    GROUP BY t1.topic_id
) as t3 
ORDER BY rating DESC, topic_id ASC

posts column gives the alltime-postcounts of the topics. It is okay, I want that. But I additionally want to get the topics' postcounts in the last 2 days. In other words, I need to get the topics' alltime-postcounts and 2-day-postcounts in one query.

table topics: topics
(source: easycaptures.com)

table messages: messages
(source: easycaptures.com)

Upvotes: 0

Views: 36

Answers (1)

Marc B
Marc B

Reputation: 360632

Something like this would work:

SELECT count(*) AS all_time, SUM(start_date > $twoDaysAgo) AS last_2_days

MySQL will auto-convert the start_date comparison boolean value to an integer 0 or 1, and then sum up those 1's, effectively giving you the count you need.

Upvotes: 1

Related Questions