MYSQL GroupBy need to be taken only values

Question

I was trying to get data from one table and those data are classified based on the last six months. so I was able to get each month's data once I do grouping I am facing some issues.

The above table I want to group it. but when I do the grouping it is taking only the first row and the rest of the row values are missing. the result is coming like this

I am expecting an answer like this

MY SQL Query that I am using

SET @date='2021-07-31 23:59:59';
SELECT *
FROM (
       SELECT pd.user_id
            , oc.date_added
            , CASE
                WHEN Date_format(`date`,'%M-%Y')=date_format(@date- interval 1 month,'%M-%Y') 
                THEN sum(purchase_point)
              END AS m1
            , CASE
                WHEN date_format(`date`,'%M-%Y')=date_format(@date- interval 2 month,'%M-%Y') 
                THEN sum(purchase_point)
              END AS m2
            , CASE
                WHEN date_format(`date`,'%M-%Y')=date_format(@date- interval 3 month,'%M-%Y') 
                THEN sum(purchase_point)
              END AS m3
            , CASE
                WHEN date_format(`date`,'%M-%Y')=date_format(@date- interval 4 month,'%M-%Y') 
                THEN sum(purchase_point)
              END AS m4
            , CASE
                WHEN date_format(`date`,'%M-%Y')=date_format(@date- interval 5 month,'%M-%Y') 
                THEN sum(purchase_point)
              END) AS m5
            , CASE
                WHEN date_format(`date`,'%M-%Y')=date_format(@date- interval 6 month,'%M-%Y') 
                THEN sum(purchase_point)
              END AS m6
       FROM 64_point_details AS pd
        LEFT JOIN 64_users AS us ON us.user_id=pd.user_id
        LEFT JOIN oc_customer AS oc ON oc.customer_id=us.store_id
       WHERE date_added<=date_format(@date- interval 6 month,'%Y-%m-%d 23:59:59')
             AND pd.user_id=1358
             AND pd.date BETWEEN date_format(@date- interval 6 month,'%Y-%m-%d 23:59:59') AND @date
       GROUP BY  pd.user_id,
                 date_format(`date`,'%Y-%m')
       HAVING    sum(purchase_point)>0
       ORDER BY  date_format(`date`,'%Y-%m') ASC ) AS t
GROUP BY t.user_id;

have u try this [solution](https://stackoverflow.com/questions/1918346/mysql-group-by-date-when-using-datetime/1918363#1918363) ? — shubham, Sep 20 '21 at 05:11
Provide reproduceable example (CREATE TABLE + INSERT INTO + desired output in textual form). PS. Your query is not logically correct - simply enable ONLY_FULL_GROUP_BY and ensure. You must use `MAX(Mx)` in the output list of the outer SELECT, not `SELECT *`. — Akina, Sep 20 '21 at 05:32
Your question mentions one table, but your query references three. That is confusing. — Gordon Linoff, Sep 20 '21 at 11:31

score 0 · Answer 1 · answered Sep 20 '21 at 11:30

You seem to want simple aggregation. Your query is much more complicated than your data, but I can speculate:

SELECT pd.user_id, oc.date_added,
       SUM(CASE WHEN Date_format(`date`, '%M-%Y') = date_format(@date- interval 1 month, '%M-%Y') 
                THEN purchase_point
           END) AS m1,
       SUM(CASE WHEN Date_format(`date`, '%M-%Y') = date_format(@date- interval 2 month, '%M-%Y') 
                THEN purchase_point
           END) AS m2,
       . . . 
FROM 64_point_details pd LEFT JOIN
     64_users us
     ON us.user_id = pd.user_id
     oc_customer oc
     ON oc.customer_id = us.store_id
WHERE date_added <= date_format(@date- interval 6 month,'%Y-%m-%d 23:59:59') AND
      pd.user_id = 1358 AND
      pd.date BETWEEN date_format(@date- interval 6 month,'%Y-%m-%d 23:59:59') AND @date
GROUP BY pd.user_id, oc.date_added
HAVING sum(purchase_point) > 0

MYSQL GroupBy need to be taken only values

1 Answers1