On Mar 29, 2005, at 10:38 PM, Jason Dixon wrote:
Anyways, I have a simple schema that stores some IP accounting data. I'm attempting to extract the data, grouping by the service type ("label"), and also grouping by each 24-hour window/day from the datetime column ("timestamp"). I'm not sure how to accomplish this type of query, but I'm hoping someone here will help me do this in SQL and avoid having to do it in my Perl code. Thanks in advance!
I finally managed to grind out what I'm looking for. This is primarily for the archives...
mysql> SELECT label, SUM(bytes) as bytes, SUM(packets) as packets, SUM(evals) as evals, DATE_FORMAT(timestamp, '%Y-%m-%d') as date from stats GROUP BY label, date ORDER BY date asc, bytes desc;
+------------------------+---------+---------+-------+------------+
| label | bytes | packets | evals | date |
+------------------------+---------+---------+-------+------------+
| other-outbound | 630864 | 1104 | 149 | 2005-03-27 |
| other-inbound | 583571 | 5748 | 149 | 2005-03-27 |
| site1-inbound-ssh | 112657 | 756 | 16 | 2005-03-27 |
| site1-inbound-http | 38700 | 165 | 16 | 2005-03-27 |
| site1-inbound-default | 0 | 0 | 149 | 2005-03-27 |
| site1-outbound-default | 0 | 0 | 149 | 2005-03-27 |
| site1-outbound-ssh | 0 | 0 | 0 | 2005-03-27 |
| site1-outbound-http | 0 | 0 | 0 | 2005-03-27 |
| site2-inbound | 0 | 0 | 149 | 2005-03-27 |
| site2-outbound | 0 | 0 | 149 | 2005-03-27 |
| other-outbound | 637008 | 1148 | 151 | 2005-03-28 |
| other-inbound | 591209 | 5792 | 151 | 2005-03-28 |
| site1-inbound-ssh | 112657 | 756 | 16 | 2005-03-28 |
| site5-inbound | 1900 | 23 | 149 | 2005-03-28 |
| site3-inbound | 0 | 0 | 149 | 2005-03-28 |
| site3-outbound | 0 | 0 | 149 | 2005-03-28 |
| site4-inbound | 0 | 0 | 149 | 2005-03-28 |
| site4-outbound | 0 | 0 | 149 | 2005-03-28 |
| site5-outbound | 0 | 0 | 149 | 2005-03-28 |
| site1-inbound-default | 0 | 0 | 151 | 2005-03-28 |
| other-inbound | 1561931 | 11173 | 895 | 2005-03-29 |
| site1-inbound-http | 77400 | 330 | 31 | 2005-03-29 |
| other-outbound | 46024 | 330 | 895 | 2005-03-29 |
| site5-inbound | 1900 | 23 | 1046 | 2005-03-29 |
| site1-outbound-default | 0 | 0 | 1046 | 2005-03-29 |
| site1-outbound-ssh | 0 | 0 | 0 | 2005-03-29 |
| site1-outbound-http | 0 | 0 | 0 | 2005-03-29 |
| site2-inbound | 0 | 0 | 1046 | 2005-03-29 |
| site2-outbound | 0 | 0 | 1046 | 2005-03-29 |
| site3-inbound | 0 | 0 | 1046 | 2005-03-29 |
| site3-outbound | 0 | 0 | 1046 | 2005-03-29 |
| site4-inbound | 0 | 0 | 1046 | 2005-03-29 |
| site4-outbound | 0 | 0 | 1046 | 2005-03-29 |
| site5-outbound | 0 | 0 | 1046 | 2005-03-29 |
| site1-inbound-default | 0 | 0 | 895 | 2005-03-29 |
| site1-inbound-ssh | 0 | 0 | 15 | 2005-03-29 |
+------------------------+---------+---------+-------+------------+
36 rows in set (0.01 sec)
-- Jason Dixon DixonGroup Consulting http://www.dixongroup.net
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
