Error GROUP BY Dates MysQL

Question

I am trying to group by dates 2 different tables. However, something is going wrong as I am not able to group by dates.

I have 2 tables First "Budget" and Second "Sales Performance By Region" The Idea is to show the Budget for all the months (12 months) and each month we have data we will see the Sales for each specific month.

SELECT  
      BB.RG,
      BB.YEAR, 
      DATE_FORMAT( BB.MONTH, '%M' ) AS `MONTH`,
      SPR.Bookings,
      SPR.`Bookings PY`,
      SPR.Billing,
      SPR.`Billing PY`,
      SPR.`GP Amt`, 
      SPR.`GP Amt PY`, 
      SUM( BB.BUDGET ) AS BUDGET 
   FROM
      BRANCH_BUDGET BB
         LEFT JOIN  Sales_Performance_Region SPR
            ON BB.MONTH = SPR.MONTH
   WHERE
          BB.RG =  'FE'
      AND SPR.RG =  'FE'
      AND BB.YEAR =  '2018'
   GROUP BY
      BB.MONTH

Above you can see the code. Instead of showing the 12 Months due to I only have data for 2 Months is showing January and February.

I hope the question makes sense. Can you guide me?

"`Sales_Performance_Region.RG = FE`" effectively turns it into an `INNER JOIN`. — PM 77-1, Feb 06 '18 at 15:40
You typically GROUP BY the columns you select, except those who are arguments to set functions. — jarlh, Feb 06 '18 at 15:40
It's not a good habit to not GROUP BY all non-aggregated columns. Your code won't even run in any other rdbms. It doesn't make sense. — Eric, Feb 06 '18 at 17:41

score 0 · Answer 1 · answered Feb 06 '18 at 15:48

Whenever you invoke a GROUP BY after a WHERE statement or any other aggregate function inside a SELECT statement you will need to treat everything inside the select statement as an aggregate, otherwise you will face problems:

In your code:

SELECT  `BRANCH_BUDGET`.`RG` ,  
        `BRANCH_BUDGET`.`YEAR` AS  `YEAR` , 
        `DATE_FORMAT(  `BRANCH_BUDGET`.`MONTH` ,  '%M' ) AS  `MONTH` , 
        `Sales_Performance_Region`.`Bookings` ,  
        `Sales_Performance_Region`.`Bookings PY` ,  
        `Sales_Performance_Region`.`Billing` , 
        `Sales_Performance_Region`.`Billing PY` ,  
        `Sales_Performance_Region`.`GP Amt` ,  
        `Sales_Performance_Region`.`GP Amt PY` , 
        SUM( `BRANCH_BUDGET`.`BUDGET` ) AS  `BUDGET` 
FROM (
      `BRANCH_BUDGET` 
       LEFT JOIN  `Sales_Performance_Region` ON (  `BRANCH_BUDGET`.`MONTH` =  
       `Sales_Performance_Region`.`MONTH` )
       )
WHERE  `BRANCH_BUDGET`.`RG` =  'FE'
        AND  `Sales_Performance_Region`.`RG` =  'FE'
        AND  `BRANCH_BUDGET`.`YEAR` =  '2018'
GROUP BY  `BRANCH_BUDGET`.`MONTH`

You only group by BRANCH_BUDGET.MONTH, but do not group by any other column or perform an aggregate function on any other column (save BRANCH_BUDGET.BUDGET). Thus your output is faulty in that it only returns two months.

Thanks, Benjamin, However, I just need to group by Month because the rest of the variables are the Same. If I group by Branch then I will multiply the value of the different Branches. And that's wrong. — Eduardo Montero, Feb 06 '18 at 15:53

score 0 · Answer 2 · answered Feb 06 '18 at 16:07

First thanks for the comments I want to post the result in case someone is interested.

The issue was: I didn't join the Region (RG) from both tables. So the result should look like the code below:

SELECT  `BRANCH_BUDGET`.`RG` ,
`BRANCH_BUDGET`.`YEAR` AS  `YEAR` ,
DATE_FORMAT(  `BRANCH_BUDGET`.`MONTH` , '%M' ) AS  `MONTH` , 
`Sales_Performance_Region`.`Bookings` , 
`Sales_Performance_Region`.`Bookings PY` ,
`Sales_Performance_Region`.`Billing` , 
`Sales_Performance_Region`.`Billing PY` , 
`Sales_Performance_Region`.`GP Amt` , 
`Sales_Performance_Region`.`GP Amt PY` , 
SUM( `BRANCH_BUDGET`.`BUDGET` ) AS  `BUDGET` 
FROM (
 `BRANCH_BUDGET` 
LEFT JOIN  `Sales_Performance_Region` 
    ON (  `Sales_Performance_Region`.`MONTH` =  `BRANCH_BUDGET`.`MONTH` ) 
    AND (  `Sales_Performance_Region`.`RG` =  `BRANCH_BUDGET`.`RG` )
)
WHERE  `BRANCH_BUDGET`.`RG` =  'FE'
AND  `BRANCH_BUDGET`.`YEAR` =  '2018'
GROUP BY  `BRANCH_BUDGET`.`MONTH`

Error GROUP BY Dates MysQL

2 Answers2