Anomaly


Hi everyone,

My problem is that I have a table for which I need the totals, the table contains 6 columns, 3 of them are a character and the other 3 are money amounts:

Group1 | Group2 | Group3 | TaxGroup1 | TaxGroup2 | TaxGroup3
A A1 A1a 1.00 .50 .25
B B1 B1a 2.00 .30 .45
A A1 <empty> 1.20 .25 .00

As you can see the TaxGroup1 is connected with Group1 and so on, also the other groups are sub-groups. I need to be able to generate a cursor to show the totals of each group.

I hope my explanation helps.

Thank you.



Re: Trouble totaling up fields for a report

ControlNode


So the total for group1 should just be the sum of taxgroup1 (4.20), group2 would be 1.05, and so on or is it more complex than that





Re: Trouble totaling up fields for a report

Anomaly

The Totals should look like this :

Group1 | Group2 | Group3 | TaxGroup1 | TaxGroup2 | TaxGroup3
A A1 A1a 2.20 .75 .25
B B1 B1a 2.00 .30 .45


Where A, A1, and A1a exist in the first table there should be 1 record with 3 totals in it.

Perhaps a SORT to sort all of the data into a specific order and then add them up





Re: Trouble totaling up fields for a report

Naomi Nosonovsky

Anomaly wrote:
Hi everyone,

My problem is that I have a table for which I need the totals, the table contains 6 columns, 3 of them are a character and the other 3 are money amounts:

Group1 | Group2 | Group3 | TaxGroup1 | TaxGroup2 | TaxGroup3
A A1 A1a 1.00 .50 .25
B B1 B1a 2.00 .30 .45
A A1 <empty> 1.20 .25 .00

As you can see the TaxGroup1 is connected with Group1 and so on, also the other groups are sub-groups. I need to be able to generate a cursor to show the totals of each group.

I hope my explanation helps.

Thank you.

We can get:

select Group1, sum(TaxGroup1) as TotalTaxGroup1 from myTable group by 1 into cursor cur1

select Group2, sum(TaxGroup2) as TotalTaxGroup2 from myTable group by 1 into cursor cur2

select Group3, sum(TaxGroup3) as TotalTaxGroup3 from myTable group by 1 into cursor cur3

To connect them you may try to connect first based on Group1, second based on Group2, third based on Group3. So, you can combine now all steps in 1 SQL, e.g.

select g.Group1, g.Group2, g.Group3, cur1.TotalTGroup1, cur2.TotalTGroup2, cur3.TotalTGroup3 from MyTable g ;

inner join (select Group1, sum(TaxGroup1) as TotalTaxGroup1 from myTable group by 1) cur1 on g.Group1 = cur1.Group1 ;

inner join ....

Or you may have to use left join in case you don't have data in one of the columns. The idea is from the top of my head, not tested.





Re: Trouble totaling up fields for a report

Anomaly

Actually I could put them all into separate cursors like you have done there but name their fields the same and APPEND them all into 1 cursor with 2 columns.




Re: Trouble totaling up fields for a report

Olaf Doschke

The problem of that is, that A,A1, <empty> will not merge with A,A1,A1a

to get the sums for A1 and B1 etc right you need

Select Group1, Group2,;

Sum(TaxGroup1) as TaxGroup1,;

Sum(TaxGroup2) as TaxGroup2;

from myTable group by 1,2

To get A,B etc right

Select Group1,;

Sum(TaxGroup1) as TaxGroup1;

from myTable group by 1

But I'm not sure how you would want to merge these seperate results, if there was A1b or A2.






Re: Trouble totaling up fields for a report

Naomi Nosonovsky

You know your requirements better, so I'm glad if you would be able to proceed from that idea.