MySQL monthly Sale of last 12 months including months with no Sale

by

Thanks for @pankaj hint, Here i resolved it via this query…

SELECT 
    SUM(IF(month="Jan", total, 0)) AS 'Jan',
    SUM(IF(month="Feb", total, 0)) AS 'Feb',
    SUM(IF(month="Mar", total, 0)) AS 'Mar',
    SUM(IF(month="Apr", total, 0)) AS 'Apr',
    SUM(IF(month="May", total, 0)) AS 'May',
    SUM(IF(month="Jun", total, 0)) AS 'Jun',
    SUM(IF(month="Jul", total, 0)) AS 'Jul',
    SUM(IF(month="Aug", total, 0)) AS 'Aug',
    SUM(IF(month="Sep", total, 0)) AS 'Sep',
    SUM(IF(month="Oct", total, 0)) AS 'Oct',
    SUM(IF(month="Nov", total, 0)) AS 'Nov',
    SUM(IF(month="Dec", total, 0)) AS 'Dec',
    SUM(total) AS total_yearly
    FROM (
SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM cart
WHERE date <= NOW() and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")) as sub

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. Rank function in MySQL
  3. MySQL select 10 random rows from 600K rows fast
  4. Count the number of occurrences of a string in a VARCHAR field?
  5. Find nearest latitude/longitude with an SQL query
  6. Simulate lag function in MySQL
  7. Get record counts for all tables in MySQL database
  8. Most efficient way to get table row count
  9. How do I check if a column is empty or null in MySQL?
  10. Use MySQL spatial extensions to select points inside circle
  11. Subqueries with EXISTS vs IN – MySQL
  12. What is this operator in MySQL?
  13. SELECT * WHERE NOT EXISTS
  14. 60 million entries, select entries from a certain month. How to optimize database?
  15. Is it possible to GROUP BY multiple columns using MySQL?
  16. MySQL concatenation operator
  17. SQL Group By with an Order By
  18. How create json format with group-concat mysql?
  19. MySQL LIKE query with underscore
  20. MySql – Way to update portion of a string?
  21. Using reserved words in column names
  22. MySQL INNER JOIN select only one row from second table
  23. Storing IPv6 Addresses in MySQL
  24. What is the difference between SQL and MySQL? [closed]
  25. how to use a like with a join in sql?
  26. Get total hours worked in a day mysql
  27. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  28. MySQL update statement to store ranking positions
  29. How to convert a varchar column type to date type without losing the dates
  30. MySQL Multiple Joins in one query?

Leave a Comment