Sum，Case，Union，Group by：Php Mysql

Question

SUM CASE not showing proper details

Sample date :

below is data with group by prdid

wghvalue    prdid  addby  cutequl
0.4         57546    me     1
0.6         71256    me     1
0.2         68754    me     1

below is data when i remove group by prdid

wghvalue    prdid  addby  cutequl
0.1         57546    me     1
0.1         57546    me     1
0.2         57546    me     1
0.1         71256    me     1
0.5         71256    me     1
0.2         68754    me     1

one strange thing :

in above sample data

prdb.order  cutid.order
    2           2
    3           2
    4           4

so if i am writing query

prdb.order - cutid.order as newval

i am getting value like ... 2-2 = 0 , 3-2 = 1 , 4-4 =0

but same for

sum(case when prdb.order - cutid.order = 0 then 1 else 0 end) as newval

getting value like ... 3 , 2 , 1

i think bove value 3,2,1 not getting group by prdid

below is query

SELECT 
    sum(t1.count) as count,
    sum(t1.wghvalue) as wghvalue,
    t1.addby,
    SUM(t1.cutequl) as cutequl
FROM 
    (SELECT  
        COUNT(pl.id) as count,
        SUM(pl.wghvalue) as wghvalue
        addby,
        SUM(CASE 
            WHEN prdb.cutaname = cutid.cutname
                 AND prdb.order = cutid.order
                 AND prdb.order - cutid.order = 0  
            THEN 1 
            else 0 
        end) as cutequl
    FROM 
        product AS prod
        LEFT JOIN producta AS prda ON prod.id = prda.id
        LEFT JOIN prdoctb AS prdb ON prod.pid = prda.pid 
        LEFT JOIN ( SELECT id, wghvalue, addby, prdid
                    FROM prdentrya
                    WHERE 1=1 
                    GROUP BY prdid
                    UNION
                    SELECT id, wghvalue, addby, prdid
                    FROM prdentryb
                    WHERE 1=1
                    GROUP BY prdid
                   ) AS pl ON( pl.prdid = prda.prdid  )
        LEFT JOIN cut AS cutid ON prdb.cutaname = cutid.cutname  
    WHERE 
        pl.aadby = 103 
    GROUP BY 
        pl.prdid
    ORDER BY
        pl.prdid ASC,
        pl.aadby ASC 
    ) t1
group by t1.addby

Output coming from above query is :

count | wghvalue | addby | cutequl 
3     |  1.2     |  me   |   6

What i should get as below

count | wghvalue | addby | cutequl 
3     |  1.2     |  me   |   3

i think GROUP BY prdid is not working in sum case , how i can include that in sum case.

thanks

Answer 1

First Try To store result into temp Table. Then use SUM operation on It.

                CREATE TEMPORARY TABLE IF NOT EXISTS TempTemp AS
                (
                    SELECT  
                        COUNT(pl.id) as count,
                        SUM(pl.wghvalue) as wghvalue
                        addby,
                        SUM(CASE 
                            WHEN prdb.cutaname = cutid.cutname
                                 AND prdb.order = cutid.order
                                 AND prdb.order - cutid.order = 0  
                            THEN 1 
                            WHEN prdb.order is null or prdb.cutaname  is null THEN 1
                            else 0 
                        end) as cutequl
                    FROM 
                        product AS prod
                        LEFT JOIN producta AS prda ON prod.id = prda.id
                        LEFT JOIN prdoctb AS prdb ON prod.pid = prda.pid 
                        LEFT JOIN ( SELECT id, wghvalue, addby, prdid
                                    FROM prdentrya
                                    WHERE 1=1 
                                    GROUP BY prdid
                                    UNION
                                    SELECT id, wghvalue, addby, prdid
                                    FROM prdentryb
                                    WHERE 1=1
                                    GROUP BY prdid
                                   ) AS pl ON( pl.prdid = prda.prdid  )
                        LEFT JOIN cut AS cutid ON prdb.cutaname = cutid.cutname  
                    WHERE 
                        pl.aadby = 103 
                    GROUP BY 
                        pl.prdid
                    ORDER BY
                        pl.prdid ASC,
                        pl.aadby ASC 
                )


                SELECT 
                    sum(t1.count)  , sum(t1.wghvalue) ,t1.addby,SUM(t1.cutequl) , From 
                    TempTemp t1 
                    group by addby