divided all quantity of items in a part group by the part that ends in -12 of the same part group

[kat35601]

Hello, I have a  query that I need to divided all quantity of items in a part group by the part that ends in -12 of the same part group

so every part in a 110 partgroup needs to be divided by the Qty of 110-12

example 110-01 with a Qty of 214 would be divided by the qty of part 110-12 which is 355 for a results of .6028.. or 60.28% and then move to the next number in the

Part group 110-02 and so on.

 

I have attached a jpeg of a spreadsheet that shows this.

 

[kat35601]

<?php
$connect =odbc_connect(removed);
if(!$connect) {
    exit("Connection Failed: " . $connect);
}

$sql="select  impPartGroupID, omlPartID, sum(omlOrderQuantity) as Qty, sum(omlExtendedPriceBase) as Total
FROM   m1_kf.dbo.SalesOrders SO
left join  m1_KF.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderId
left join m1_KF.dbo.Parts on impPartID=omlPartID
where ompCreatedDate >'01-01-2019' and impPartGroupID in (select impPartGroupID from m1_KF.dbo.Parts where impPartID like '%-12' and impPartClassID in ('FGI','FGM') )
group by  Parts.impPartGroupID, omlPartID
order by  Parts.impPartGroupID, omlPartID
";
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
echo "<table><tr>";
echo "<th>PartGroup</th>";
echo "<th>PartID</th>";
echo "<th>Qty</th>";
echo "<th>Total</th>";
 while ($row = odbc_fetch_array($result))
{

    echo "<tr><td>" .$row['impPartGroupID'] ."</td>";
    echo "<td>" .$row['omlPartID'] ."</td>";
    echo "<td>" .$row['Qty'] ."</td>";
    echo "<td>" .$row['Total'] ."</td></tr>";
 }


odbc_close($connect);

?>

My query

[Barand]

try

SELECT partgroup
     , partid
     , quantity
     , total
     , round(quantity / q12 * 100, 2) as pcent
FROM part
JOIN (
        SELECT partgroup
             , quantity as q12
        FROM part
        WHERE RIGHT(partid,2) = '12'
     ) as p12 USING (partgroup);

 

Edited March 21, 2019 by Barand

[kat35601]

This is what I have if I run the two queries separately  they work fine but together I get erroneous results. lots of the same item over and over again. I could not get the USING to work. so I am sure it's the ON statement but that's the only way I could get it to run.

 

SELECT 
pt.impPartGroupID
,sl.omlPartID
,sum(sl.omlExtendedPriceBase) AS Total
,sum(sl.omlOrderQuantity) as qty
,sum(sl.omlOrderQuantity) / q12  AS pcent
FROM m1_kf.dbo.SalesOrders so
LEFT JOIN m1_KF.dbo.SalesOrderLines sl ON omlSalesOrderID = ompSalesOrderId
LEFT JOIN m1_KF.dbo.Parts pt ON impPartID = omlPartID
join (select imppartgroupid 
,sum(omlOrderQuantity) AS q12
FROM m1_kf.dbo.SalesOrders 
LEFT JOIN m1_KF.dbo.SalesOrderLines ON omlSalesOrderID = ompSalesOrderId
LEFT JOIN m1_KF.dbo.Parts ptt ON impPartID = omlPartID
where ompCreatedDate >'03-01-2019' and impPartClassID in('FGI','FGM') and omlpartid like '%-12' 
group by impPartGroupID) as cte on omlPartGroupID=omlPartGroupID


where ompCreatedDate >'03-01-2019' and impPartClassID in('FGI','FGM')
group by  pt.impPartGroupID, omlPartID, cte.q12
order by  impPartGroupID, omlPartID

 

 

[Barand]

That appears to be a far cry from the problem that you posted.

I see you are starting a table subquery with "join (".

What I can't see is a ") aliasname" where the subquery ends.

[kat35601]

yes I took you suggestion and adapted it to work with my tables. The join aliasname is cte.

Thanks  for the help any suggestion on why I am getting the erroneous results

[kat35601]

it works thank you.

SELECT pt.impPartGroupID
,sl.omlPartID
,sum(sl.omlExtendedPriceBase) AS Total
,sum(sl.omlOrderQuantity) AS qty
,sum(sl.omlOrderQuantity) / q12 AS pcent
FROM m1_kf.dbo.SalesOrders so
LEFT JOIN m1_KF.dbo.SalesOrderLines sl ON omlSalesOrderID = ompSalesOrderId
LEFT JOIN m1_KF.dbo.Parts pt ON impPartID = omlPartID
LEFT JOIN (
SELECT imppartgroupid
,sum(omlOrderQuantity) AS q12
FROM m1_kf.dbo.SalesOrders
LEFT JOIN m1_KF.dbo.SalesOrderLines ON omlSalesOrderID = ompSalesOrderId
LEFT JOIN m1_KF.dbo.Parts ptt ON impPartID = omlPartID
WHERE ompCreatedDate > '03-01-2019'
AND impPartClassID IN (
'FGI'
,'FGM'
)
AND omlpartid LIKE '%-12'
GROUP BY impPartGroupID
) AS cte ON cte.impPartGroupID = omlPartGroupID
WHERE ompCreatedDate > '03-01-2019'
AND impPartClassID IN (
'FGI'
,'FGM'
)
GROUP BY pt.impPartGroupID
,omlPartID
,cte.q12
ORDER BY impPartGroupID
,omlPartID