asked on

calculate sum of monthly data for 6 months

I have created a SQL query which calculates the sum of particular values for every month but this query I have is showing every month in the database. I want to only return the past 6 months including the current month. That is the first thing I want to achieve.

The second, which I don't have in my query is to take the sum value for each month and deduct any refunds for the same month. Not sure if this is possible but it would be great if so. The table is called 'credit' and the column name is 'credit_value'. I am not using php as my coding language here so I am hoping it can be done with just SQL.

SELECT
    year(v.purchased),
    month(v.purchased),
    SUM(sales_price * commission) as total
FROM
    vouchers v
    INNER JOIN commission c ON v.voucher_id = c.voucher_id
WHERE
    v.status = 'Approved'
GROUP BY
    year(v.purchased),
    month(v.purchased)
ORDER BY
    year(v.purchased),
    month(v.purchased)

Open in new window

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

SELECT
    year(v.purchased),
    month(v.purchased),
    SUM(sales_price * commission) as total,
    SUM(credit_value) as total_credit_value
FROM
    vouchers v
    INNER JOIN commission c ON v.voucher_id = c.voucher_id
WHERE
    v.status = 'Approved'
    AND v.purchased >= DATEADD(month, -6, GETDATE())
GROUP BY
    year(v.purchased),
    month(v.purchased)
ORDER BY
    year(v.purchased),
    month(v.purchased)

Open in new window

slightwv (䄆 Netminder)

@Sharath,

Please confirm that works in MySQL.

SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Crazy Horse

ASKER

Thanks Julian, the error I get back is:

Unknown column 'voucher_id' in 'field list'

Taking a look to see if I can figure out which one. voucher_id doesn't exist in the credit table. The idea was just to get a SUM of credits for every month for the past 6 months.

Crazy Horse

ASKER

@Sharath, does not work in mysql unfortunately.

Julian Hansen

@Black Sulfur,

Yup, unfortunately without a view on the schema - had to guess it. Just curious as to how credits work if not linked to the voucher?

If we can see the schema we can adjust to make it work with the table definition as you have it.

Crazy Horse

ASKER

The voucher has a serial number for customers. That is the reference here ie: serial which is different to the voucher_id. The voucher_id is just the primary key in mysql.

That being said, the serial number is also in the voucher table and is also unique.

Not sure how to export something that is visual from phpmyadmin but the column names are

credit_id
serial
prod_id
credit_value
credit_reason
credit_date
credited_by

Open in new window

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>also it is showing for all months and not just the past 6 including current month

You need to add the WHERE clause I posted above to limit the rows returned.

Crazy Horse

ASKER

@Julian, thanks that actually seems like it is on the right track except it takes a really long time to execute and hasn't grouped by month, it seems to be returning every single record which would explain it taking a long time to execute. Regarding the commission table, yes it is used to calculate a percentage.

Commission table:

id:
prod_id
voucher_id
rep_id
commission

Open in new window

I am just then multiplying commission by the sales price in the voucher table.

What I am trying to do is take all the total monthly sales x by the reps commission percentage and deduct any refunds for the month to work out how much the reps cost every month.

Crazy Horse

ASKER

@slightwv, yes thank you for that, it is working on my initial query.

Crazy Horse

ASKER

Actually, I think I might have it! I also change MONTH to MONTHNAME so I could get the month name instead of number

  SELECT 
  A.year,
  A.month,
  B.total_sales,
  A.total_credit,
  B.total_sales - A.total_credit 
FROM
  (SELECT 
    SUM(credit_value) AS total_credit,
    YEAR(credit_date) AS year,
    MONTHNAME(credit_date) AS month 
  FROM
    credit 
    WHERE credit_date > DATE_SUB(now(), INTERVAL 5 MONTH)
  GROUP BY year, month) A 
  LEFT JOIN 
    (SELECT 
      SUM(sales_price * commission_perc / 1.15) AS total_sales,
      YEAR(purchased) AS year,
      MONTHNAME(purchased) AS month 
    FROM
      vouchers v 
      LEFT JOIN commission c 
        ON v.voucher_id = c.voucher_id 
        WHERE v.status = 'Approved'
        AND v.purchased > DATE_SUB(now(), INTERVAL 5 MONTH)
    GROUP BY year, month) B 
    ON A.month = B.month 
    AND A.year = B.year

Open in new window

slightwv (䄆 Netminder)

>> I have tried in like this but it seems to ignore it and shows me all months still

The LEFT JOIN says give me everything on the "left" side of the join along with things on the "right" that match the join conditions.

So, you are getting everything from the credit table.

Do you need a LEFT JOIN?

This is the best graphical representation of JOINs and how they work. If you aren't familiar with JOINs, I would print out the final image and keep it handy.

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Crazy Horse

ASKER

Thanks for everyones help, I was able to get a solution in the end as a result and it's working well.