Need help aggregating

[LJones]

Hello,

I've worked quite a lot with aggregate functions in
queries, but cannot seem to accomplish this one.

From one query, I get the following 4 records:

BILL PART PriorPD CurrPD PriorQTY CurrQTY
8825-1 8078 0406 0 25 0
8825-2 8078 0 0407 0 25
8803 8078 0406 0407 22 21
8811 8078 0406 0407 5 5

With my next query I want to combine the first two records
above into one line, excluding the "BILL" field:

PART PriorPD CurrPD PriorQTY CurrQTY
8078 0406 0407 25 25
8078 0406 0407 22 21
8078 0406 0407 5 5

Nothing I have tried so far has produced the desired
result. Any help is appreciated!

Thanks,
LJ

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, ... you can't exclude the Bill column 'cuz that is the unique
identifier that will allow you to combine the rows. I'd probably do it
like this:

SELECT Left(Bill,4) As _Bill, PriorPD, CurrPD, Sum(PriorQTY) AS
SumPriorQTY, Sum(CurrQTY) as SumCurrQTY
FROM query_name
GROUP BY Left(Bill,4) As _Bill, PriorPD, CurrPD
ORDER BY 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRk6zoechKqOuFEgEQJm5wCfQZNq/InZtPM+tU6ymYv02vmwpkgAoIE7
GHHaDxkKMQOFzkP/0riDj8Mb
=HTDy
-----END PGP SIGNATURE-----

 

[LJones]

Thanks, it worked! Now I can go home
/LJ

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, ... you can't exclude the Bill column 'cuz that is the unique
identifier that will allow you to combine the rows. I'd probably do it
like this:

SELECT Left(Bill,4) As _Bill, PriorPD, CurrPD, Sum (PriorQTY) AS
SumPriorQTY, Sum(CurrQTY) as SumCurrQTY
FROM query_name
GROUP BY Left(Bill,4) As _Bill, PriorPD, CurrPD
ORDER BY 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRk6zoechKqOuFEgEQJm5wCfQZNq/InZtPM+tU6ymYv02vmwpk gAoIE7
GHHaDxkKMQOFzkP/0riDj8Mb
=HTDy
-----END PGP SIGNATURE-----



.