Same SQL Error in a few queries

Question

Possible Duplicate:
Referencing field values between queries

I have 3 similar queries that when run give me the same error

You tried to execute a query that does not include the specified expression 'CompanyName' as part of an aggregate function

I kind of understand the issue but i dont know how to resolve it.. Here are 1 of the 3 queries..

    SELECT qb1.CompanyName, qb1.AssetName, qb1.Year, 
    (qb2.MPPOil-SUM(IIf(qb1.DatapointID=2003,
qb1.DatapointValue*1000000,
qb1.DatapointValue))) AS UnallocatedLossesOIL

    FROM PEBaseQuery AS qb1 
    INNER JOIN PE_MPPOilRevised AS qb2 
    ON qb1.AssetName = qb2.AssetName
    WHERE qb1.DatapointID In (2032,2034,2042,2036,2030,2028);

This is based on the calculation:

Unallocated losses = MPP - (GAS × 1000000) - (Wellwork + Annual shutdown maintenance + Export + Plant + Reservoir losses)

I used similar syntax in another table which runs smoothly :S Heres the code:

SELECT qb1.CompanyName, qb1.AssetName, qb1.Year, (qb1.DatapointValue/qb2.DatapointValue)*1000000 AS TRIPerMillionManHours
FROM HSEBaseQuery AS qb1 
INNER JOIN HSEBaseQuery AS qb2 
ON qb1.Assetname=qb2.AssetName
WHERE qb2.DatapointID=310005 AND qb1.DatapointID<>qb2.DatapointID;

Why does one work and the other doesnt?? Please help!

score 2 · Accepted Answer · answered Aug 28 '12 at 15:10

2

The first query contains an aggregate function, and anything not part of the calculated value which is being summed must be contained within a group by clause:

SELECT qb1.CompanyName
       , qb1.AssetName
       , qb1.Year
       , (qb2.MPPOil - SUM( IIf( qb1.DatapointID=2003,
                                 qb1.DatapointValue*1000000,
                                 qb1.DatapointValue))) AS UnallocatedLossesOIL
FROM  PEBaseQuery AS qb1 
      INNER JOIN PE_MPPOilRevised AS qb2 ON qb1.AssetName = qb2.AssetName
WHERE qb1.DatapointID In (2032,2034,2042,2036,2030,2028)
GROUP BY qb1.CompanyName
         , qb1.AssetName
         , qb1.Year
         , qb2.MPPOil;

As HansUp said, the second query doesn't contain the aggregate function sum(), so no group by is required.

answered Aug 28 '12 at 15:10

CJR

200
1
1
8

Thanks, the query compiles and runs however the calculated column values are way off, could this be due to grouping by MPPOil?? (MPPOil is the calculated column of data in another query, one value per company/asset/year) – Magda Aug 28 '12 at 16:29
Possibly, it depends on whether you have to roll up your Gas*1000000 rows up to the same grain as your MPPOil rows, or if you want to include MPPOil in your sum() clause instead of the group by clause. – CJR Aug 28 '12 at 16:48
Also, please note something fishy: In your select statements, you have an iif() statement dependent on DatapointID being 2003. However the IN statement in your where clause filters any rows that would fulfill that requirement, so it will never be evaluated. – CJR Aug 28 '12 at 16:49
Yesss thats it! Thanks a lot, i cant believe i missed that – Magda Aug 28 '12 at 20:13

Same SQL Error in a few queries

1 Answers1