Link to home
Start Free TrialLog in
Avatar of michael_krell
michael_krell

asked on

Help with query

I have the following query

SELECT tblProductionGross.grossid as RunID,tblProductionGross.GrossMSF as Gross,sum(tblProductionNet.NetMSF) as Net
FROM 
  tblProductionGross
  LEFT JOIN 
  GKBL1PROD...tblProductionNet 
  ON (tblProductionGross.GrossId = tblProductionNet.GrossId)
where Timebegin >= '11/5/2012 6:00 PM' and TimeEnd <= '11/9/2012 6:00 AM'
group by tblProductionGross.grossid,tblProductionGross.GrossMSF

Open in new window



that returns the results here

RunID      Gross      Net
23438      0      
23439      0      
23440      52.57479      36.72
23441      190.1936      189.108
23442      1062.36      1061.208
23443      28.77977      27.54
23444      341.179      334.152
23445            3.672
23446      271.9201      252.486
23447      150.8911      132.352
23448      295.4676      290.048
23449      880.7854      848.64
23450      300.7621      298.112
23451      424.6873      408
23452            19.584
23453      228.4359      221.952
23454      1057.223      955.536
23455      80.83609      75.072
23457      201.5259      

What i need is the sum of the net column divided by the sum of the gross column to return a percentage. Probably seems silly but i am building some custom reporting and this is the only way i can figure to get the data the way i need it. I am sure i can do this using temporary tables and stuff, but i am unable to use that kind of code in the reporting software.

Thanks
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

something like this?

SELECT sum(tblProductionGross.GrossMSF) / sum(tblProductionNet.NetMSF)
FROM
  tblProductionGross
  LEFT JOIN
  GKBL1PROD...tblProductionNet
  ON (tblProductionGross.GrossId = tblProductionNet.GrossId)
where Timebegin >= '11/5/2012 6:00 PM' and TimeEnd <= '11/9/2012 6:00 AM'
Avatar of michael_krell
michael_krell

ASKER

The problem i have with that is that i get duplicates in the Gross column because i dont have that group by.
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
excellent. works great.