Hi,
I wrote a query with multiple subqueries , and I tried to join them with UNION ALL to get my favorite result ; every subquery works fine as individual module but when I combine them with UNION ALL I get strange result .
This is a short story about main query :
I have some projects , each project has some products that identified with (leafCode,leafName,colorsCode) in DB .Also each project has some other elements like (orderAmount) that identify related project order quantity and (orderQuota) that is ration of product quantity that is not related to that project; Produced products could be placed in some locations as (polish,pack,warehouse,sent) .
Now I need to calculate progress of every defined project with using above fields .
This is code details :
>SELECT ft.projectName
> ,ft.leafCode
> ,ft.leafName
> ,ft.colorsCode
> ,ft.polish
> ,ft.pack
> ,COALESCE(ft.warehouse, 0) AS `warehouse`
> ,ft.sent
> ,COALESCE(ft.orderQuota, 0) AS `orderQuota`
> ,(COALESCE(ft.polish, 0) + COALESCE(ft.pack, 0) + COALESCE(ft.warehouse, 0)) - (COALESCE(ft.orderQuota, 0)) AS `available`
> ,COALESCE(ft.orderAmount, 0) AS `orderAmount`
> ,COALESCE(COALESCE(ft.orderAmount, 0)) - ((COALESCE(ft.polish, 0) + COALESCE(ft.pack, 0) + COALESCE(ft.warehouse, 0) + COALESCE(ft.sent, 0)) - (COALESCE(ft.orderQuota, 0))) AS `projectRemained`
> ,ROUND((((((COALESCE(ft.polish, 0) + COALESCE(ft.pack, 0) + COALESCE(ft.warehouse, 0) + COALESCE(ft.sent, 0)) - COALESCE(ft.orderQuota)) / COALESCE(ft.orderAmount, 0))) * 100), 2) AS `Progress(%)`
>FROM (
>SELECT projects.projectName
> ,rs_leaves.leafCode
> ,rs_leaves.leafName
> ,rs_colors.colorsCode
> ,NULL AS `polish`
> ,NULL AS `pack`
> ,NULL AS `warehouse`
> ,SUM(Distinct assign_details.assAmount) AS `sent`
> ,projects_details.orderQuota
> ,projects_details.orderAmount
>FROM assign_details
>INNER JOIN rs_leaves
>ON assign_details.leafName = rs_leaves.leafID
>INNER JOIN rs_colors
>ON assign_details.leafColor = rs_colors.colorsID
>INNER JOIN projects_details
>ON assign_details.projectID = projects_details.projectID
>INNER JOIN projects
>ON projects_details.projectID = projects.projectID
>Group By rs_leaves.leafCode,
> rs_leaves.leafName,
> rs_colors.colorsCode,
> projects_details.projectID
>UNION ALL
>SELECT projects.projectName
> ,rs_leaves.leafCode
> ,rs_leaves.leafName
> ,rs_colors.colorsCode
> ,NULL AS `polish`
> ,NULL AS `pack`
> ,NULL AS `warehouse`
> ,NULL AS `sent`
> ,projects_details.orderQuota
> ,projects_details.orderAmount
>FROM projects_details
>INNER JOIN rs_leaves
>ON rs_leaves.leafID = projects_details.leafName
>INNER JOIN rs_colors
>ON rs_colors.colorsID = projects_details.leafColor
>INNER JOIN projects
>ON projects.projectID = projects_details.projectID
>INNER JOIN assign_details
>ON projects_details.projectID = assign_details.projectID
>Group By projects_details.projectID, rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
>UNION ALL
>SELECT NULL AS `projectName`
> ,rs_leaves.leafCode
> ,rs_leaves.leafName
> ,rs_colors.colorsCode
> ,COALESCE(Sum(patine_tbl.receivedNum), 0) - COALESCE(Sum(Case
> When sent_receive_tbl.stationID = '6' Then sent_receive_tbl.sentNum
> Else Null End), 0) As `ploish`
> ,NULL AS `pack`
> ,NULL AS `warehouse`
> ,NULL AS `sent`
> ,NULL AS `orderQuota`
> ,NULL AS `orderAmount`
>FROM patine_tbl
>INNER JOIN rs_leaves
>ON rs_leaves.leafID = patine_tbl.leafID
>INNER JOIN rs_colors
>ON rs_colors.colorsID = patine_tbl.colorsID
>LEFT JOIN sent_receive_tbl
>ON sent_receive_tbl.leafID = rs_leaves.leafID
>AND sent_receive_tbl.colorsID = rs_colors.colorsID
>Group By rs_leaves.leafCode,
> rs_leaves.leafName,
> rs_colors.colorsCode
>UNION ALL
>SELECT NULL AS `projectName`
> ,rs_leaves.leafCode
> ,rs_leaves.leafName
> ,rs_colors.colorsCode
> ,NULL AS `polish`
> ,COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.receivedNUM Else Null End), 0)
> - COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.sentNum Else Null End), 0) As `pack`
> ,NULL AS `warehouse`
> ,NULL AS `sent`
> ,NULL AS `orderQuota`
> ,NULL AS `orderAmount`
>FROM sent_receive_tbl
>INNER JOIN rs_leaves
>ON sent_receive_tbl.leafID = rs_leaves.leafID
>INNER JOIN rs_colors
>ON sent_receive_tbl.colorsID = rs_colors.colorsID
>Group By rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
>UNION ALL
>SELECT NULL AS `projectNmae`
> ,NULL AS `leafCode`
> ,NULL AS `leafName`
> ,NULL AS `colorsCode`
> ,NULL AS `polish`
> ,NULL AS `pack`
> ,tt.warehouse
> ,NULL AS `sent`
> ,NULL AS `orderQuota`
> ,NULL AS `orderAmount`
> FROM (SELECT st.leafCode,
> st.colorsCode,
> COALESCE(Sum(st.INPUT), 0) As `INPUT`,
> COALESCE(Sum(st.OUTPUT), 0) As OUTPUT,
> COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
> FROM (SELECT rs_leaves.leafCode
> ,rs_colors.colorsCode
> ,Sum(receipt_details.recAmount) As `INPUT`
> ,NULL As `OUTPUT`
> FROM receipt_details
> INNER JOIN rs_leaves
> ON rs_leaves.leafID = receipt_details.leafName
> INNER JOIN rs_colors
> ON rs_colors.colorsID = receipt_details.leafColor
> Group By rs_leaves.leafCode, rs_colors.colorsCode
> UNION All
> SELECT rs_leaves.leafCode
> ,rs_colors.colorsCode
> ,NULL As `INPUT`
> ,Sum(assign_details.assAmount) As `OUTPUT`
> FROM assign_details
> INNER JOIN rs_leaves
> ON rs_leaves.leafID = assign_details.leafName
> INNER JOIN rs_colors
> ON rs_colors.colorsID = assign_details.leafColor
> Group By rs_leaves.leafCode, rs_colors.colorsCode
> ) As st
> Group By leafCode,colorsCode
> ) AS tt
> ) AS ft
>Group By ft.projectName,ft.leafCode
>,ft.leafName
>,ft.colorsCode;
And this screenshot is the result :
Result has some issues that I was not successful to resolve them :
1 - Why SQL return NULL values in first row (except warehouse) and why warehouse is NULL in other rows ?
2 - Why SQL returns NULL value for projectName in some fields , regard to available data in DB I know that every product in every place has a related project so I shouldn`t have NULL value as projectName ?
3- Why when I remove “Distinct” statement after “SUM” , SQL returns wrong values for (sent) field ? :injured:
4- And catastrophe :sick: , most important fields like warehouse,polish,pack, orderAmount,orderQuota have wrong output .
I need to view all available projects and their unique details through all related data to those projects for calculating project progress ;
Could you please help me ?