January 23, 2012 at 3:24 pm
I am having trouble constructing an aggregation query which shows the total number of invoices, total sales, minimum sale, largest sale, and average sale (without wanting to reach for a no-no cursor). I believe the solution involves a correlated subquery, at least for the MAX, MIN, and AVG aggregations.
What I've done is build the queries that'll retrieve each component seperately, but you'll see this breaks down when it comes to MAX, MIN, AVG, because I have analyzed the tables to know which cus_code made the most sales and fed that to the query (I need a query that will do the analysis).
Needed result
Total Invoices ----Total Sales ---- Minimum Sales ---- Largest Sale---- Average Sale
8 ------------------1126.03 -----------34.97 -------------- 444.00 -----------225.21
---query for total number of invoices
select COUNT(inv_number)
from invoice
---query for total sales
select SUM (line_units * line_price)
from line
---customer with max sales
select SUM (line_units * line_price)
from line l
join invoice i
on l.inv_number = i.inv_number
join customer c
on i.cus_code = c.cus_code
where c.cus_code = 10011;
---customer with min sales
select SUM (line_units * line_price)
from line l
join invoice i
on l.inv_number = i.inv_number
join customer c
on i.cus_code = c.cus_code
where c.cus_code = 10015;
---average sales
cannot figure out how to do because it is per customer and each customer has several inv_numbers
---DDL to recreate environment
CREATE TABLE CUSTOMER (
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.859985351562');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.190002441406');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.929992675781');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.550003051758');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0');
/* -- */
CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE datetime
);
INSERT INTO INVOICE VALUES('1001','10014','1/16/2008');
INSERT INTO INVOICE VALUES('1002','10011','1/16/2008');
INSERT INTO INVOICE VALUES('1003','10012','1/16/2008');
INSERT INTO INVOICE VALUES('1004','10011','1/17/2008');
INSERT INTO INVOICE VALUES('1005','10018','1/17/2008');
INSERT INTO INVOICE VALUES('1006','10014','1/17/2008');
INSERT INTO INVOICE VALUES('1007','10015','1/17/2008');
INSERT INTO INVOICE VALUES('1008','10011','1/17/2008');
/* -- */
CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.9899997711182');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.94999980926514');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.98999977111816');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.9500007629395');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.9500007629395');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.9899997711182');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.98999977111816');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.94999980926514');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.86999988555908');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.98999977111816');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.919998168945');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.94999980926514');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.989990234375');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.9899997711182');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.98999977111816');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.86999988555908');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.949996948242');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.94999980926514');
/* -- */
Please confirm if the answer lies in correlated subqueries, and any help you feel you can offer. Thanks. I am learning how to aggregate queries.
-
January 23, 2012 at 3:42 pm
A cte would get you what you need I believe.
;with cte as(
Select count(distinct i.inv_NUMBER) as "number of Invoices", round(SUM(line_units * line_price), 2)as "Invoice Total"
from line l
join invoice i
on l.inv_number = i.inv_number
join customer c
on i.cus_code = c.cus_code
group by c.CUS_CODE
)
select
sum([number of invoices]) [Total Invoices],
SUM([Invoice Total]) [Total Sales],
min([Invoice Total]) [Minimum Sales],
max([Invoice Total]) [Largest Sale],
AVG([invoice total]) [Average Sale]
from cte
January 23, 2012 at 5:14 pm
CREATE TABLE dbo.Customer
(
Code integer NOT NULL PRIMARY KEY,
LastName varchar(15) NOT NULL,
FirstName varchar(15) NOT NULL,
Initial char(1) NOT NULL,
AreaCode varchar(3) NOT NULL,
Phone varchar(8) NOT NULL,
Balance smallmoney NOT NULL
);
GO
INSERT dbo.Customer
(Code, LastName, FirstName, Initial, AreaCode, Phone, Balance)
VALUES
(10010,'Ramas','Alfred','A','615','844-2573',$0.00),
(10011,'Dunne','Leona','K','713','894-1238',$0.00),
(10012,'Smith','Kathy','W','615','894-2285',$345.86),
(10013,'Olowski','Paul','F','615','894-2180',$536.75),
(10014,'Orlando','Myron','','615','222-1672',$0.00),
(10015,'O''Brian','Amy','B','713','442-3381',$0.00),
(10016,'Brown','James','G','615','297-1228',$221.19),
(10017,'Williams','George','','615','290-2556',$768.93),
(10018,'Farriss','Anne','G','713','382-7185',$216.55),
(10019,'Smith','Olette','K','615','297-3809',$0.00);
CREATE TABLE dbo.Invoice
(
InvoiceNumber integer NOT NULL PRIMARY KEY,
CustomerCode integer NOT NULL REFERENCES dbo.Customer,
InvoiceDate date NOT NULL
);
GO
INSERT dbo.Invoice
(InvoiceNumber, CustomerCode, InvoiceDate)
VALUES
(1001,10014,'2008-01-16'),
(1002,10011,'2008-01-16'),
(1003,10012,'2008-01-16'),
(1004,10011,'2008-01-17'),
(1005,10018,'2008-01-17'),
(1006,10014,'2008-01-17'),
(1007,10015,'2008-01-17'),
(1008,10011,'2008-01-17');
CREATE TABLE dbo.Line
(
InvoiceNumber integer NOT NULL REFERENCES dbo.Invoice,
LineNumber int NOT NULL,
Pcode varchar(10) NOT NULL,
Units tinyint NOT NULL,
LinePrice smallmoney NOT NULL,
PRIMARY KEY (InvoiceNumber, LineNumber)
);
GO
INSERT dbo.Line
(InvoiceNumber, LineNumber, Pcode, Units, LinePrice)
VALUES
(1001,1,'13-Q2/P2',1,14.99),
(1001,2,'23109-HB',1,9.95),
(1002,1,'54778-2T',2,4.99),
(1003,1,'2238/QPD',1,38.95),
(1003,2,'1546-QQ2',1,39.95),
(1003,3,'13-Q2/P2',5,14.99),
(1004,1,'54778-2T',3,4.99),
(1004,2,'23109-HB',2,9.95),
(1005,1,'PVC23DRT',12,5.87),
(1006,1,'SM-18277',3,6.99),
(1006,2,'2232/QTY',1,109.92),
(1006,3,'23109-HB',1,9.95),
(1006,4,'89-WRE-Q',1,256.99),
(1007,1,'13-Q2/P2',2,14.99),
(1007,2,'54778-2T',1,4.99),
(1008,1,'PVC23DRT',5,5.87),
(1008,2,'WR3/TT3',3,119.95),
(1008,3,'23109-HB',1,9.95);
-- Per customer totals
SELECT
InvoiceCount = COUNT_BIG(DISTINCT inv.InvoiceNumber),
SalesTotal = SUM(line.Units * line.LinePrice)
FROM dbo.Customer AS cus
JOIN dbo.Invoice AS inv ON
inv.CustomerCode = cus.Code
JOIN dbo.Line AS line ON
line.InvoiceNumber = inv.InvoiceNumber
GROUP BY
cus.Code;
WITH CustomerTotals AS
(
SELECT
InvoiceCount = COUNT_BIG(DISTINCT inv.InvoiceNumber),
SalesTotal = SUM(line.Units * line.LinePrice)
FROM dbo.Customer AS cus
JOIN dbo.Invoice AS inv ON
inv.CustomerCode = cus.Code
JOIN dbo.Line AS line ON
line.InvoiceNumber = inv.InvoiceNumber
GROUP BY
cus.Code
)
SELECT
[Total Invoices] = COUNT_BIG(*),
[Total Sales] = SUM(InvoiceCount),
[Minimum Sales] = MIN(SalesTotal),
[Largest Sales] = MAX(SalesTotal),
[Average Sale] = AVG(SalesTotal)
FROM CustomerTotals;
January 24, 2012 at 10:05 am
roryp and Paul, the CTE solution provided works great. Thanks.
-
January 24, 2012 at 11:26 pm
Joe, I addressed most of your points with the code I posted, and comments made on other threads from this user. There is a learning process going on here; please take it down a notch so your advice is seen as a learning opportunity rather than overly-harsh criticism. You were new to this once, remember.
January 25, 2012 at 2:29 am
Hello Celko,
I can see how the points you make are important when it’s your task to design a database, but that is not what I am focusing on here. For sure I don’t want to be ignorant and want to be in compliance with federal and international regulations. My brain was just not there and my answer to your questions is that “I didn’t know”.
I had the time to look at your points tonight. You said:
1. No keys and no way to have keys. OK. that’s true, and I know that you’re supposed to have them. But, I know at least two production environments (big companies) that do not use FKs anywhere in their database. One database supporting huge customers has 1400 tables (and controls dirty data with stored procedures that cleanse before and after import) and the second (internal app) has 100 tables, and the boss just doesn’t think it’s that important. Please speak to this.
2. Float for currency amount is…illegal(read the GAAP and EU rules for Euros). Quoting you from another sqlcentral post “The use of FLOAT or REAL for money is a crime under EU regulations for Euros and under GAAP in the United States.”and you again on the http://www.talkroot.com forum, "FLOAT: Dangerous and illegal (see EU Euro rules and GAAP) This is how an account with 0.00000123 in it becomes a Dun Letter for $0.00.”, but I can’t find an independent resource readily. Will you kindly point out an on-line resource for me to quickly read up on this …. One which takes me directly to the point?
3. The balance is not a customer attribute it is a computation. Are you are saying that it would more logically be a computed column, derived by evaluating columns in other tables? That seems to make sense.
4. The use of VARCHAR for fixed length strings will destroy data integrity. Why?
5. Regarding international format. Well couldn’t find an authoritative site, but this one talks to the format. http://docs.bulletin.net/display/GLOSS/International+Number+Format
6. ISO date format for SQL? This format is most often: YYYY-MM-DD and this link talks to that point http://www.iso.org/iso/support/faqs/faqs_widely_used_standards/widely_used_standards_other/date_and_time_format.htm
7. Other points, I don’t have time to look at, at this point.
Oh, I *will* become a SQL developer.;-)
-
January 25, 2012 at 2:47 am
Celko, I would like to read your books, but now I'm afraid that it will feel like you're yelling at me, and I usually have a voice in my head that I attribute to the author, that needs to feel nice and mentor like, like he loves me like saint and cares for my learning. Just a thought for you when you address people (wimps like me).
-
January 25, 2012 at 7:26 am
Joe's posts on here have convinced me never to buy his books. He believes that he is being authoritative when he's just being authoritarian. He states opinion as if it were fact. He doesn't back up his opinions with reasons. He references standards and laws without providing links to said standards and laws. He's hypocritical--bashing dialects when the dialect is T-SQL, but praising the MySQL dialect. In short, he's dogmatic.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply