SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating Financial Data

    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:

    Code:
    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:
    Code:
    +------------+--------------+-------+------+----------+---------+--------------+----------------+
    | 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:
    Code MySQL:
    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!

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it with help from Stackoverflow

    Code MySQL:
    SELECT m.launch_date, m.name, m.type, m.frequency, m.cost,
    SUM(o.order_amount) as revenue,
    COUNT(o.order_amount) as orders,
    SUM(o.order_amount)/COUNT(o.order_amount) as average_order
    FROM media m
    JOIN orders o
    ON LOWER(o.referral) = CONCAT(LOWER(m.type), ': ' ,LOWER(m.name))
    WHERE (DATE(o.order_date) BETWEEN '2013-06-24' AND '2013-06-30')
    GROUP BY m.name, m.type


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •