MySQL: Show monthly sales and include months where no sales where made [closed]

Closed. This question needs debugging details. It is not currently accepting answers.

---

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 3 years ago.

Improve this question

I need true professional DBA help! I can't find a reasonable solution using LEFT JOIN, UNION, UNION ALL or IFNULL(salesblah, 0) on a table of dates against a table of sales data. Running queries on the sales data solo is out since there could be gaps on a sales rep. Sure, I could make an array in PHP to sum the data by date, but there's gotta to be an elegant solution out there that I can't see and this personal challenge has worn me out. There has got to be a short and sweet solution for this.

With MySQL 5.5.15 on the server, an excerpt of the sales data (currently 800,000 rows and growing daily) looks like this:

    Rep         Date         QtyOrdered     PriceEach
    --------------------------------------------------
    1         2011-06-05              4        1457.23
    1         2011-08-01              1        3342.54
    1         2011-08-11             12         112.23
    2         2011-05-02              3        2654.23
    2         2011-08-23             22         423.43
    .            ...                ...            ...

The other table is just a column of dates from 2000-01-01 to 2034-12-31

Using (or not using) the table of dates, when I call a rep 1 in a query, how can I get this result:

YYYY-MM      Total Sales
------------------------
2010-08               0
2010-09               0
2010-10               0
2010-11               0
2010-12               0
2011-01               0
2011-02               0
2011-03               0
2011-04               0
2011-05               0
2011-06         5828.92
2011-07               0
2011-08         4960.14

I'm really hoping to put away the unabridged book of impolite words. Thank you for your talent.

EDIT: The left join solution fro开发者_开发知识库m Derek omits the non-zero months. Nerf's sub query method sums all into one month. I believe it's the GROUP BY which is killing the non-zero months.

Thank you! I believe the simplest course is to have PHP run the results through an array. Ultimately, this is to build a chart for a range of months through GD and the hybrid solution may save a step in that part.

Thank you again!

---

SELECT CONCAT_WS("-",  YEAR(date), DATE_FORMAT(date, '%m') ) AS "YYYY-MM", SUM(Total_Sales) AS "Total Sales"
FROM(
     SELECT dt.date, SUM(QtyOrdered * PriceEach) AS Total_Sales
    FROM sales_data sd
         LEFT JOIN datetbl dt
         ON sd.Date = dt.date
    WHERE sd.rep = 1 AND dt.date BETWEEN '2011-08-01' AND '2011-08-31'
        GROUP BY dt.date
         )  AS derived_table

---

This should get you pretty close. Not tested, but logic should be correct.

select 
   cast(year(dt.date) as varchar(4)) + '-' + cast(month(dt.date) as varchar(2)),
   sum(QtyOrdered * PriceEach)
from salesdata sd
right join datetbl dt
  on sd.date = dt.date
where sd.rep = 1 and dt.date between '8/1/2010' and '8/31/2011'
group by cast(year(dt.date) as varchar(4)) + '-' + cast(month(dt.date) as varchar(2))

---

I got the same issue and I got the solution without using JOIN, or any complicated stuff. The thing to avoid is to group by total. This will display all sales ordered by year, months and days, including the days where there is no sales at all (dayTotal will be equal to 0)

SELECT Year(transactions.paymentdate) AS orderYear,Month(transactions.paymentdate) AS orderMonth, Day(transactions.paymentdate) AS orderDay,
    SUM(transactions.amount) AS dayTotal
    FROM transactions
    group by orderYear desc,orderMonth desc, orderDay desc