SQLTeam.com | Weblogs | Forums

Help With Summation Query


#1

Hello All,
I have two tables as shown in the attachement.
I have written a query as shown below
`select sum(income) income,sum(expenditure) expenditure,(sum(income)-sum(expenditure)) netincome,vehicleid,incomedate,expdate
from
(
select vehicleid,income income,0 expenditure,convert(date,convert(varchar,transdate,1)) incomedate, convert(date,convert(varchar,transdate,1)) expdate from dbo.VehicleIncome
union all
select vehicleid,0 income,expenditure expenditure,convert(date,convert(varchar,transdate,1)) incomedate,convert(date,convert(varchar,transdate,1)) expdate from dbo.VehicleExpenditure
) t

group by vehicleid,incomedate,expdate`

My aim is to get only one record, since only one Vehicle details are available, but the result shows three. How do I modify my query to sum both the income and expenditure of a Vehicle, whiles including the transaction dates.
Any help would be appreciated.
Thanks

Tables:


#2

remove incomingdate and expdate from "group by" section, and use aggregate in "select" section ex. max(...) or min(...)


#3

Thanks bitsmed.
When I use max, I get only the largest income and expenditure values selected. What I need is actually a sum.
Standing by...


#4

Look at this: SELECT COALESCE(V1.vehicleid, V2.vehicleid) AS VehicleID, V1.income income, V2.expenditure expenditure, CONVERT(DATE, V1.transdate) incomedate, CONVERT(DATE, V2.transdate) expdate FROM dbo.vehicleincome V1 FULL OUTER JOIN dbo.vehicleexpenditure v2 ON V1.vehicleid = v2.vehicleid

If this gets you what you want then group and sum.


#5

Thanks dij55.
It doesn't give the desired results. It shows two records. My guess is because there are two records in the expenditure table, it brings all these but repeats the same income for both records.
Standing by...


#6

Could you show the query you used?