January 23, 2012 at 11:23 am
I am trying to modify an aggregated query slightly so that it provides one more column (called cus_code) of information. The moment I add the additional column the aggregated column, called "Invoice Total" no longer displays the correct values.
---first query which retrieves 2 columns
select inv_number, round(SUM(line_units * line_price), 2)as "Invoice Total"
from line l
group by inv_number;
---results of first query are good
100124.94
10029.98
1003153.85
100434.87
100570.44
1006397.83
100734.97
1008399.15
---second query retrieves 3 columns. Additional column called cus_code, but now Invoice Totals are off
select c.cus_code, i.inv_number, round(SUM(line_units * line_price), 2)as "Invoice Total"
from line l
join invoice i
on l.inv_number = l.inv_number
join customer c
on i.cus_code = c.cus_code
group by c.cus_code, i.inv_number;
---results of second query, you see that Invoice Total now totals up all invoice numbers.
cus_codeinv_numberInvoice Total
1001410011126.03
1001110021126.03
1001210031126.03
1001110041126.03
1001810051126.03
1001410061126.03
1001510071126.03
1001110081126.03
---DDL to recreate environment (creates 3 tables)
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');
/* -- */
Can you please show me what to do in my second query, so that Invoice Totals reflect only the toal by inv_number?
-
January 23, 2012 at 11:35 am
I believe your join to the invoice table is wrong. You have on l.inv_number = l.inv_number. You should have on l.inv_number = i.inv_number
January 23, 2012 at 11:48 am
You are correct!:laugh: So happy! Thank you so much!
---corrected query
select c.cus_code, i.inv_number, 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, i.inv_number;
----correct results
cus_codeinv_numberInvoice Total
1001110029.98
10011100434.87
100111008399.15
100121003153.85
10014100124.94
100141006397.83
10015100734.97
10018100570.44
-
January 23, 2012 at 12:11 pm
You will find it easier to avoid errors like this if you (a) choose more meaningful table alias names; (b) always include schema names; (c) always include alias names...and so on:
SELECT
cust.cus_code,
inv.inv_number,
[Invoice Total] = ROUND(SUM(line.line_units * line.line_price), 2)
FROM dbo.line AS line
JOIN dbo.invoice AS inv ON
inv.inv_number = line.inv_number
JOIN dbo.customer AS cust ON
cust.cus_code = inv.cus_code
GROUP BY
cust.cus_code,
inv.inv_number;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 23, 2012 at 3:36 pm
OK, Paul, I appreciate it very much.
-
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply