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
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
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.
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.
You know your requirements better, so I'm glad if you would be able to proceed from that idea.