sUMMING MULTIPLE COLUMNS

Transact-SQL
1

I am attempting to sum specific columns within a table and breaking each summation out by description. I understand how to do this without a join but since I need a JOIN I am running into a syntax issue.

This is what I started with and it gave me exactly what I wanted.... NOTE: this is a combined double query.

query1
query1506×739 109 KB

HOWEVER in Crystal reports with the above SQL it does not offer me the TOTAL_TAXES as a field to use. Evidently CR cannot do multi-queries or I am uncertain how to do that. I prefer not to make a sub report and insert it but if it becomes my only option then it'll have to be.

I am attempting to get the same result as the above query but in a usable return. I prefer them separated as shown in the above query

FYI: The @start_date and @end_date parameters are passed via values from the main program. I just hard coded those in for clarification.

This is as close as I have come to my desired result:

query3

I also would like to sort by Description, TOTAL_Income, then DESCRIPTION, TOTAL_TAXES

With the result show Description then amount for both INCOME and TAXES individually
What I'd like to do is split and order the results as in my original query.... This may not be possible but I am hoping.

2

that last data part is a bit odd isn't it? It is associating TOTAL_INCOME to Medicare Tax

3

you want 6 rows? why not just union all them together, although you'd probably need to add a column to know whether it's Income or expense maybe the sect_id

select d.description, sum(amount) as TotalIncomeExpense, sect_id
from ... where sect_id = 1
union all
select d.description, sum(amount) as TotalIncomeExpense, sect_id
from ... where sect_id = 53

1 Like