Sedang Disiapkan

Nested Aggregate SQL Query

I want to create a single view that returns JobID, TotalBillableAmount, TotalBilled and TotalAlreadyUnbilled. Part1 subquery returns total billing for that job, regardless of billdate. Part2 returns TotalBilled when the DateBilled is not null and Part 3 returns TotalUnbilled when the DateBilled is null. I am getting what appear to be multiple sums when I execute this.

Tables are sjob, xitem and rates. sjob/xitem are linked by jobid, xitem/rates are linked by item.

My current code is below:

select [url removed, login to view], sum([url removed, login to view]) as TotalBillableAmount, sum([url removed, login to view]) as TotalAlreadyBilled, sum([url removed, login to view]) as TotalUnbilled from sjob,

(

SELECT [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], ([url removed, login to view] * [url removed, login to view]) AS totaljb

FROM [url removed, login to view]

INNER JOIN

[url removed, login to view] ON [url removed, login to view] = [url removed, login to view]

INNER JOIN

[url removed, login to view] ON [url removed, login to view] = [url removed, login to view]

) part1,

(

SELECT [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view] * [url removed, login to view] AS totalab

FROM [url removed, login to view]

INNER JOIN

[url removed, login to view] ON [url removed, login to view] = [url removed, login to view]

INNER JOIN

[url removed, login to view] ON [url removed, login to view] = [url removed, login to view]

where DATEBILLED is not null

) part2,

(

SELECT [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view] * [url removed, login to view] AS totaltb

FROM [url removed, login to view]

INNER JOIN

[url removed, login to view] ON [url removed, login to view] = [url removed, login to view]

INNER JOIN

[url removed, login to view] ON [url removed, login to view] = [url removed, login to view]

where DATEBILLED is null

) part3

where [url removed, login to view] = [url removed, login to view] and [url removed, login to view] = [url removed, login to view] and [url removed, login to view] = [url removed, login to view]

group by [url removed, login to view]

Kemahiran: SQL

Lihat lebih lanjut: inner join nested aggregate, SQL Query, aggregate, nested sql, sql nested query, nested, nested query sql, create tables sql 2012, nested query, jobid, sql query sum, sql multiple tables, jobid 2012, sql view, job aggregate, sql create view, getting total sql, create view sql, create sql view, php html query sql, uusing data adaptor aspnet query sql server database, parent child query sql server, excel 2007 sum multiple sums, net query sql mobile table example, parent child query sql

Tentang Majikan:
( 9 ulasan ) Lawrenceville, United States

ID Projek: #4081935

Dianugerahkan kepada:

mlambrichs

Professional freelancer. DBA. Please read my PM.

$40 USD dalam sehari
(3 Ulasan)
2.1