[AccessD] Sum a union query

Works perfect. Thanks Group.

Chester Kaup
Information Management Technician
IT-MidContinent/MidContinent Business Unit
CTN 8-687-7415  
Outside 432-687-7415

No trees were killed in the sending of this message. However a large
number of electrons were terribly inconvenienced. 




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, December 03, 2004 6:02 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Sum a union query


Hi Chester

Seems like a Group By is missing.
And sorting isn't needed. 
Also note UNION ALL.
Try this:

<SQL>

  SELECT 
    ACTVDATE, 
    Sum(Exp1), 
    Sum(Exp2)
  FROM
    (SELECT 
      ACTVDATE, 
      Sum(VOLUME_SS) AS Exp1, 
      Sum([VOLUME_SS])*0.335849 AS Exp2
    FROM 
      T9CAT_GAS_MTR_READINGS
    WHERE 
      GASMTRIDNO IN (605954,605955,605956,425030) 
      AND
      ACTVDATE>=#10/1/2004#
    GROUP BY 
      ACTVDATE
    UNION ALL
    SELECT 
      ACTVDATE, 
      Sum([VOLUME_SS]*-1) AS Exp1, 
      Sum([VOLUME_SS])*-0.335849 AS Exp2
    FROM 
      T9CAT_GAS_MTR_READINGS
    WHERE 
      GASMTRIDNO=605957 
      AND  
      ACTVDATE>=#10/1/2004#
    GROUP BY
      ACTVDATE)
  GROUP BY 
    ACTVDATE
  ORDER BY 
    ACTVDATE;

</SQL>

/gustav

>>> kaupca at chevrontexaco.com 02-12-2004 23:33:55 >>>
Can I do this in a union query? Generates error of no aggregate function
for ACTVDATE.

Select ACTVDATE, Sum(Exp1), Sum(Exp2)
From
(SELECT ACTVDATE, Sum(VOLUME_SS) as Exp1, Sum([VOLUME_SS])*0.335849 as
Exp2 FROM T9CAT_GAS_MTR_READINGS WHERE GASMTRIDNO IN
(605954,605955,605956,425030) and
ACTVDATE>=#10/1/2004#
GROUP BY ACTVDATE
ORDER BY ACTVDATE
UNION
SELECT ACTVDATE, Sum([VOLUME_SS]*-1)as Exp1, Sum([VOLUME_SS])*-0.335849
as Exp2 FROM T9CAT_GAS_MTR_READINGS WHERE GASMTRIDNO=605957 and
ACTVDATE>=#10/1/2004#) GROUP BY ACTVDATE ORDER BY ACTVDATE;

Chester Kaup
Information Management Technician
IT-MidContinent/MidContinent Business Unit
CTN 8-687-7415  
Outside 432-687-7415

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com