Mega Code Archive
A query that uses three subqueries
<![CDATA[
5>
6>
7>
8> create table Bankers(
9> BankerID Integer,
10> BankerName VARCHAR(20),
11> BankerContactLName VARCHAR(20),
12> BankerContactFName VARCHAR(20),
13> BankerCity VARCHAR(20),
14> BankerState VARCHAR(20),
15> BankerZipCode VARCHAR(20),
16> BankerPhone VARCHAR(20)
17> )
18> GO
1>
2> insert into Bankers values (1, 'ABC Inc.','Joe','Smith','Vancouver','BC','11111','111-111-1111');
3> GO
(1 rows affected)
1> insert into Bankers values (2, 'DEF Inc.','Red','Rice', 'New York', 'DE','22222','222-222-2222');
2> GO
(1 rows affected)
1> insert into Bankers values (3, 'HJI Inc.','Kit','Cat', 'Paris', 'CA','33333','333-333-3333');
2> GO
(1 rows affected)
1> insert into Bankers values (4, 'QWE Inc.','Git','Black','Regina', 'ER','44444','444-444-4444');
2> GO
(1 rows affected)
1> insert into Bankers values (5, 'RTY Inc.','Wil','Lee', 'Toronto', 'YU','55555','555-555-5555');
2> GO
(1 rows affected)
1> insert into Bankers values (6, 'YUI Inc.','Ted','Larry','Calgary', 'TY','66666','666-666-6666');
2> GO
(1 rows affected)
1> insert into Bankers values (7, 'OIP Inc.','Yam','Act', 'San Franc','FG','77777','777-777-7777');
2> GO
(1 rows affected)
1> insert into Bankers values (8, 'SAD Inc.','Hit','Eat', 'Orland', 'PO','88888','888-888-8888');
2> GO
(1 rows affected)
1> insert into Bankers values (9, 'DFG Inc.','Sad','Lee', 'Wisler', 'PL','99999','999-999-9999');
2> GO
(1 rows affected)
1> insert into Bankers values (0, 'GHJ Inc.','Bit','Lee', 'Ticker', 'MN','00000','000-000-0000');
2> GO
(1 rows affected)
1>
2>
3> create table Billings (
4> BankerID INTEGER,
5> BillingNumber INTEGER,
6> BillingDate datetime,
7> BillingTotal INTEGER,
8> TermsID INTEGER,
9> BillingDueDate datetime ,
10> PaymentTotal INTEGER,
11> CreditTotal INTEGER
12>
13> );
14> GO
1>
2> INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, '2003-05-02', 165, 1,'2005-04-12',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, '1999-03-12', 165, 1,'2005-04-18',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, '2000-04-23', 165, 1,'2005-04-17',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, '2001-06-14', 165, 1,'2005-04-18',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, '2002-07-15', 165, 1,'2005-04-19',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, '2003-08-16', 165, 1,'2005-04-20',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, '2004-09-17', 165, 1,'2005-04-21',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, '2005-10-18', 165, 1,'2005-04-22',123,321);
2> GO
(1 rows affected)
1>
2> SELECT Summary1.BankerState, Summary1.BankerName, TopInState.SumOfBillings
3> FROM
4> (SELECT V_Sub.BankerState, V_Sub.BankerName,
5> SUM(I_Sub.BillingTotal) AS SumOfBillings
6> FROM Billings AS I_Sub JOIN Bankers AS V_Sub
7> ON I_Sub.BankerID = V_Sub.BankerID
8> GROUP BY V_Sub.BankerState, V_Sub.BankerName) AS Summary1
9> JOIN
10> (SELECT Summary2.BankerState,
11> MAX(Summary2.SumOfBillings) AS SumOfBillings
12> FROM
13> (SELECT V_Sub.BankerState, V_Sub.BankerName,
14> SUM(I_Sub.BillingTotal) AS SumOfBillings
15> FROM Billings AS I_Sub JOIN Bankers AS V_Sub
16> ON I_Sub.BankerID = V_Sub.BankerID
17> GROUP BY V_Sub.BankerState, V_Sub.BankerName) AS Summary2
18> GROUP BY Summary2.BankerState) AS TopInState
19> ON Summary1.BankerState = TopInState.BankerState AND
20> Summary1.SumOfBillings = TopInState.SumOfBillings
21> ORDER BY Summary1.BankerState
22> GO
BankerState BankerName SumOfBillings
-------------------- -------------------- -------------
BC ABC Inc. 165
CA HJI Inc. 165
DE DEF Inc. 165
ER QWE Inc. 165
FG OIP Inc. 165
MN GHJ Inc. 165
PL DFG Inc. 165
PO SAD Inc. 165
TY YUI Inc. 165
YU RTY Inc. 165
(10 rows affected)
1>
2> drop table Bankers;
3> drop table Billings;
4> GO
1>
]]>