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
– PM 77-1, Commented Feb 6, 2018 at 15:40
You typically GROUP BY the columns you select, except those who are arguments to set functions. — jarlh
– jarlh, Commented Feb 6, 2018 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
– Eric, Commented Feb 6, 2018 at 17:41

isakbob · Accepted Answer · 2018-02-06 15:48:04Z

0

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.

answered Feb 6, 2018 at 15:48

isakbob

1,5592 gold badges22 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eduardo Montero Over a year ago

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 · Accepted Answer · 2018-02-06 16:07:36Z

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`

Collectives™ on Stack Overflow

Error GROUP BY Dates MysQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related