Group data in query and avoid using SP

I have some data that looks like the following and I want to group it by
accountID so that I can get the sums of PaidAmt and charge. The problem is
that I have different paymentDates so I can't group it. And I can't
eliminate paymentDate because the valid date for each accountID is needed.
So, for example, the first group total would be: 32482 | 2005-12-30 | 20.00
| 172.00. I also need to create a separate field that shows the sum of
PaidAmt - sum of charge (per accountID) but I figure that I could do that
calc with Crystal Reports.
This can be done using a SP but I don't want to write unless it's completely
necessary.
Thanks.
accountid paymentDate PaidAmt charge
-- ---
32482 2005-12-30 00:00:00.000 20.0000 145.0000
32482 NULL .0000
12.0000
32482 NULL .0000
15.0000
32498 2005-12-29 00:00:00.000 25.0000 145.0000
32498 NULL .0000
12.0000
32498 NULL .0000
15.0000
32658 2005-12-30 00:00:00.000 15.0000 145.0000
32658 NULL .0000
12.0000
32658 NULL .0000
15.0000
32691 2005-12-22 00:00:00.000 15.0000 145.0000
32691 NULL .0000
12.0000
32691 NULL .0000
15.0000
32705 2005-12-23 00:00:00.000 10.0000 135.0000
32705 NULL .0000
12.0000
32705 NULL .0000
15.0000You can do this by creating a subquery to calc the sums by accountid, and
then creating a second query to attach the date. For example
CREATE VIEW vsubAmtPaid AS
SELECT accountid, SUM(PaidAmt) AS totalPaid, SUM(charge) AS totalCharge
FROM table1
GROUP By accountid
CREATE VIEW vwAmtPaid AS
SELECT t.accountid, paymentDate, totalPaid, totalCharge
FROM table1 t INNER JOIN vsubAmtPaid v ON t.accountid = v.accountid
WHERE NOT (paymentDate IS NULL)
I haven't tested this, but it should get you the results you posted, with
one exception. You mentioned you needed a separate field for the sum of the
amount paid and amount charged, but those totals are 20 and 172 respectively
.
Did you by chance want the original, un-summed amounts to be included in
addition to the sums?
Also, I'm assuming that the final results should return only one record per
accountid. If that's not the case, eliminate the WHERE clause from the final
query. Also, based on your sample data I assumed each accountid has only one
record where the paymentDate is not null, so I used that in the second query
to filter on. If this is not the case, you'll need to figure another way to
filter.
"VMI" wrote:

> I have some data that looks like the following and I want to group it by
> accountID so that I can get the sums of PaidAmt and charge. The problem is
> that I have different paymentDates so I can't group it. And I can't
> eliminate paymentDate because the valid date for each accountID is needed.
> So, for example, the first group total would be: 32482 | 2005-12-30 | 20.0
0
> | 172.00. I also need to create a separate field that shows the sum of
> PaidAmt - sum of charge (per accountID) but I figure that I could do that
> calc with Crystal Reports.
> This can be done using a SP but I don't want to write unless it's complete
ly
> necessary.
> Thanks.
> accountid paymentDate PaidAmt charge
> -- ---
> 32482 2005-12-30 00:00:00.000 20.0000 145.0000
> 32482 NULL .0000
> 12.0000
> 32482 NULL .0000
> 15.0000
> 32498 2005-12-29 00:00:00.000 25.0000 145.0000
> 32498 NULL .0000
> 12.0000
> 32498 NULL .0000
> 15.0000
> 32658 2005-12-30 00:00:00.000 15.0000 145.0000
> 32658 NULL .0000
> 12.0000
> 32658 NULL .0000
> 15.0000
> 32691 2005-12-22 00:00:00.000 15.0000 145.0000
> 32691 NULL .0000
> 12.0000
> 32691 NULL .0000
> 15.0000
> 32705 2005-12-23 00:00:00.000 10.0000 135.0000
> 32705 NULL .0000
> 12.0000
> 32705 NULL .0000
> 15.0000
>
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:592684
On Mon, 27 Mar 2006 11:38:24 -0500, "VMI" <vonchi_m AT yahoo DOT com>
wrote:

>I have some data that looks like the following and I want to group it by
>accountID so that I can get the sums of PaidAmt and charge. The problem is
>that I have different paymentDates so I can't group it. And I can't
>eliminate paymentDate because the valid date for each accountID is needed.
>So, for example, the first group total would be: 32482 | 2005-12-30 | 20.00
>| 172.00. I also need to create a separate field that shows the sum of
>PaidAmt - sum of charge (per accountID) but I figure that I could do that
>calc with Crystal Reports.
>This can be done using a SP but I don't want to write unless it's completel
y
>necessary.
Hi VMI,
If the data you present here is representative for your real data, you
can use:
SELECT accountid, MAX(paymentDate), SUM(PaidAmt), SUM(charge)
FROM YourTable
GROUP BY accountid
(untested - see www.aspfaq.com/5006 if you prefer a tested reply).
Hugo Kornelis, SQL Server MVP