Please I need some guide on how to sum my NewConverts column as seen in the table below.

I have 2 tables with names tbl_cmleader and tbl_cmreport. I combine the 2 tables to run this query as seen below:

SELECT mdate AS MDate, WEEK(mdate) AS MWeek,  WEEK(NOW()) AS CWeek, nconvt AS 
NewConverts, tbl_cmreport.cml_id, tbl_cmleader.grp_id FROM tbl_cmreport INNER JOIN 
tbl_cmleader ON tbl_cmreport.mem_id = tbl_cmleader.mem_id WHERE tbl_cmleader.grp_id = 12 
and mdate = '2022-07-08' ORDER BY mdate DESC

After running this query, I get the result as seen below:

However, I can't use this result to achieve what I want because the values are much. So I add a Group By to help reduce the contents and give me the right values as seen in the query below.

SELECT mdate AS MDate, WEEK(mdate) AS MWeek,  WEEK(NOW()) AS CWeek, nconvt AS 
NewConverts, tbl_cmreport.cml_id, tbl_cmleader.grp_id FROM tbl_cmreport INNER JOIN 
tbl_cmleader ON tbl_cmreport.mem_id = tbl_cmleader.mem_id WHERE tbl_cmleader.grp_id = 12 
and mdate = '2022-07-08' GROUP BY mdate, tbl_cmleader.cml_id ORDER BY mdate DESC

At the end I get this results below

This is the final table I want. Now from this table, I want to SUM the NewConverts column to get a value of 9 but instead I am getting 36.

I run this query below to SUM the NewConverts column. The value I expect after the summation is a 9 but instead I am getting 36.

SELECT mdate AS MDate, WEEK(mdate) AS MWeek,  WEEK(NOW()) AS CWeek, SUM(nconvt) AS 
NewConverts, tbl_cmreport.cml_id, tbl_cmleader.grp_id FROM tbl_cmreport INNER JOIN 
tbl_cmleader ON tbl_cmreport.mem_id = tbl_cmleader.mem_id WHERE tbl_cmleader.grp_id = 12 
and mdate = '2022-07-08' 

I am getting 36 under column NewConverts buts that's not what I want

What I want is a 9 under column NewConverts as seen below

Please I need help on how to rewrite the query below to get the right result

SELECT mdate AS MDate, WEEK(mdate) AS MWeek,  WEEK(NOW()) AS CWeek, SUM(nconvt) AS 
NewConverts, tbl_cmreport.cml_id, tbl_cmleader.grp_id FROM tbl_cmreport INNER JOIN 
tbl_cmleader ON tbl_cmreport.mem_id = tbl_cmleader.mem_id WHERE tbl_cmleader.grp_id = 12 
and mdate = '2022-07-08' 

Thank you in advance Michael

CodePudding user response：

Your queries will fail with sql_mode=only_full_group_by enabled. It is not a good practice to disable it.

To get your desired result in the query which gives the final table that you want , you could apply an outer query doing the SUM

SELECT MDate,
       MWeek,
       CWeek,
       SUM(NewConverts) as tot_NewConverts,
       max(cml_id) as max_cml_id,
       grp_id
FROM ( 
       SELECT    mdate AS MDate, 
                 WEEK(mdate) AS MWeek,  
                 WEEK(NOW()) AS CWeek, 
                 NewConverts, 
                 tbl_cmleader.cml_id, 
                 tbl_cmleader.grp_id 
        FROM tbl_cmleader 
        WHERE tbl_cmleader.grp_id = 12 
        AND mdate = '2022-07-08' 
        GROUP BY MDate,MWeek,CWeek,cml_id,NewConverts,cml_id,grp_id
        ) as t1
GROUP BY MDate,MWeek,CWeek,grp_id;

Note,I used only one table so I removed the inner join clause. max(cml_id) is just to return the max cml_id, because of sql_mode=only_full_group_by disabled that returned value was arbitrary.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=355eb49ad446b63ee507ffe93be54440