MySQL Query Help - Do I need a subquery with a group by? Advice needed...

[matchoo]

Sometimes, I just can't get my head around the SQL I need to create.

 

Here's the table structure (blogstats)

 

CREATE TABLE  `blogstats`.`daily_totalblogs` (
  `blogSource` char(2) NOT NULL default '',
  `totblogs` bigint(21) NOT NULL default '0',
  `day` date default NULL,
  UNIQUE KEY `uq_idx` (`blogSource`,`totblogs`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

It's basically a table which contains counts for blog articles on different blog sources (techcrunch, slashdot, etc) by the day that the count was tallied.

 

First query I wrote was very straightforward:

 

select blogSource, max(day), sum(totblogs), totblogs as totblogs_inserted_last_time from daily_totalblogs group by blogSource order by totblogs_last_count desc

 

Ok, so this gets me a list of the sum of the blogs articles counted, per blog, and the number of blog articles inserted on the day that the latest blog count was made / inserted.

 

BlogSourceMax(day)sum(totblogs)totblogs_inserted_last_time

SlashDotApril 2812,5438

TechCrunchApril 261,1466

GizmodoApril 271,03310

 

What I need to get is the number of blogs that were inserted the time BEFORE the last count was made.

 

BlogSourceMax(day)sum(totblogs)blogs_inserted_last_timecount_before_last_datetotblogs_count_before_last

SlashDotApril 2812,5438April 2511

TechCrunchApril 261,1466April 229

GizmodoApril 271,03310April 108

 

Point me in the right direction?  I understand the MYSQL date functions, to calculate the previous day, but that 'time_before_last' is different for each blog network. So calculating the date is not useful in this case.  Is a subquery the answer?  HOw would that look?

 

 

 

[matchoo]

Ok, I think this would work....

select q1.cs, q1.tc, q2.tc, q3.tc, q2.d, q3.d from (select blogSource as cs, sum(totblogs) as tc from daily_totalblogs group by cs) q1 left join (select blogsource as cs, max(day) as d, totblogs as tc from daily_totalblogs group by cs) q2 on (q1.cs = q2.cs) left join (select blogsource as cs, day as d, totblogs as tc from daily_totalblogs group by cs) q3 on (q2.cs = q3.cs) where q3.d < q2.d order by q1.tc desc

 

Once again, benign neglect does the trick