How to have mySQL return data that is grouped in multiple "tiers"

Is there any way to query a mysql db and have it return a result that is grouped like this:

- 12/16/2010
  -daily
    - Ad Source 1, uniques, hits, rev
    - Ad Source 2, uniques, hits, rev
    - Ad Source 3, uniques, hits, rev
    - Ad Source 4, uniques, hits, rev
  -total
    - total uniques, total hits, total rev

- 12/15/2010
  -daily
    - Ad Source 1, uniques, hits, rev
    - Ad Source 2, uniques, hits, rev
    - Ad Source 3, uniques, hits, rev
    - Ad Source 4, uniques, hits, rev
  -total
    - total uniques, total hits, total rev

Right now if I want to get revenue on a daily basis for a site I get a result like 开发者_开发技巧this from mySQL:

• 12/16/2010, ad source 1, uniques, hits, rev
• 12/16/2010, ad source 2, uniques, hits, rev
• 12/16/2010, ad source 3, uniques, hits, rev
• 12/16/2010, ad source 4, uniques, hits, rev
• 12/15/2010, ad source 1, uniques, hits, rev
• 12/15/2010, ad source 2, uniques, hits, rev
• 12/15/2010, ad source 3, uniques, hits, rev
• 12/15/2010, ad source 4, uniques, hits, rev

Then I need to run a second query to get the total amounts for 12/16/2010 and 12/15/2010, and on top of that I need to have PHP group it by the day

The table I am querying is setup like this: ad_source_num, date, uniques, hits, rev

I feel like there must be a more efficient way of getting data returned. can anybody help :-(

---

"more efficient way" -- now you already follow the efficient way. 2 queries often is not worse than 1. And now you have the case, when it is completely true.

---

First, a query to get the grouped daily numbers:

SELECT id, ad_source_num, date, SUM(uniques) AS uniques, SUM(hits) AS hits, SUM(rev) AS rev FROM advertdata
GROUP BY date, ad_source_num ORDER BY date DESC, ad_source_num ASC

Then a second query (almost identical) to get the totals:

SELECT id, ad_source_num, date, SUM(uniques) AS uniques, SUM(hits) AS hits, SUM(rev) AS rev FROM advertdata
GROUP BY date ORDER BY date DESC, ad_source_num ASC

---

I think what you are looking for is the WITH ROLLUP declaration with your GROUP BY.

SELECT date, ad_source, SUM(uniques) AS u, SUM(hits) AS h, SUM(rev) AS r FROM tablename GROUP BY date, ad_source WITH ROLLUP

That will give you an extra "total" line whenever the date field changes.

---

Wait. Stop. Teach the man to fish, not give away the fish.

How about a design philosophy? How about a methodology? There are always problems like this to be solved.

Try writing a model that describes that data that you want. It could be useful in many situations. Sometimes you data may come from two different sources, then SQL is out zee window.

 public function get_crap($start_date, $end_date)
    {
    $return = array();
    foreach (valid_date_range($start_date, $end_date) as $date)
         {
         $return[$date] = daily_crap_from($date, $this->db);
         }
     return $return;
    } 

 static function daily_crap_from($mm_dd_yyyy, $db) 
   {
   $return = array();
   $db->query("select * from daily_crap_lines where day like '".$mm_dd_yyyy."'")
   while ($c = $db->next_record())
      {
      $return[] = $c;
      }
    return $return;
   }