Each week, my client runs radio spots and email campaigns through a third party marketing group to promote his product. He’d like to know how much he’s making/spending each week, and to have the data broken down for him. The table data looks like this:
tablename: orders
+----------+--------------+---------------------+------------+
| order_id | order_amount | referral | order_date |
+----------+--------------+---------------------+------------+
| 1 | 16.00 | Radio: Jack Johnson | 2013-07-01 |
| 2 | 50.00 | Radio: Jack Johnson | 2013-07-02 |
| 3 | 16.00 | Radio: Jack Johnson | 2013-07-02 |
| 4 | 50.00 | Radio: Thabo Mbeki | 2013-07-03 |
| 5 | 16.00 | Email: Renewal | 2013-07-08 |
| 6 | 50.00 | Other | 2013-07-08 |
| 7 | 16.00 | Email: Renewal | 2013-07-08 |
| 8 | 50.00 | Radio: Thabo Mbeki | 2013-07-09 |
+----------+--------------+--------------+-------------------+
tablename: media
+----------+-------+-------+--------------+-------+------------+
| media_id | spots | cost | name | type | date |
+----------+-------+-------+--------------+-------+------------+
| 1 | 10 | 10000 | Jack Johnson | radio | 2013-07-01 |
| 2 | 5 | 5000 | Thabo Mbeki | radio | 2013-07-01 |
| 3 | 10 | 10000 | Jack Johnson | radio | 2013-07-08 |
| 4 | 5 | 5000 | Thabo Mbeki | radio | 2013-07-08 |
| 5 | 1 | 250 | Renewal | email | 2013-07-08 |
+----------+---------------+--------------+--------------------+
He’s looking to see the data structured like this:
+------------+--------------+-------+------+----------+---------+--------------+----------------+
| week | name | type |spots | cost | revenue | total orders | avg. per order |
+------------+--------------+-------+------+----------+---------+--------------+----------------+
| 2013-07-01 | Jack Johnson | radio | 10 | 10000 | 82.00 | 3 | 27.33 |
| 2013-07-01 | Thabo Mbeki | radio | 5 | 5000 | 50.00 | 1 | 50.00 |
+------------+--------------+-------+------+----------+---------+--------------+----------------+
I’ve been looking at the mysql site trying out different methods of joins and then looking into subqueries. I’ve got this working to show me the referrals for today’s date in groups:
SELECT referral,
COUNT( * ),
SUM( order_amount )
FROM orders
WHERE DATE( order_date ) = CURDATE()
GROUP BY referral
ORDER BY referral
I know the data could be cleaner, but this is what they have. If if would be easier to clean this up and do it another way, I’d like to hear it. So far, getting the data from one table into another is screwing with my head. Any help would be greatly appreciated!