aggregation, group by clause, need help tweakng query

Question

Post reply

aggregation, group by clause, need help tweakng query

xenophilia

SSCommitted

Points: 1891
More actions
January 22, 2012 at 12:24 am

#255220

Will someone please show me how to tweak this query so that cus_code and associated "Total Purchases" appear only once? When I add a group by clause, it generates error.
---this query...
select c.cus_code, c.cus_balance, sum(l.line_units * l.line_price) over (partition by c.cus_code) as "Total Purchases"
from customer c
join invoice i
on c.cus_code = i.cus_code
join line l
on i.inv_number = l.inv_number
----generates these results....
cus_codecus_balanceTotal Purchases
100110443.999972343445
100110443.999972343445
100110443.999972343445
100110443.999972343445
100110443.999972343445
100110443.999972343445
10012345.86153.849998474121
10012345.86153.849998474121
10012345.86153.849998474121
100140422.769987106323
100140422.769987106323
100140422.769987106323
---desired results
cus_codecus_balanceTotal Purchases
100110443.999972343445
10012345.86153.849998474121
100140422.769987106323
---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');
/* -- */
-

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

This works fine for me (though I get two extra rows for 10015 and 10018).

select c.cus_code, c.cus_balance, sum(l.line_units * l.line_price) as "Total Purchases"

from customer c

join invoice i

on c.cus_code = i.cus_code

join line l

on i.inv_number = l.inv_number

GROUP BY

c.cus_code, c.Cus_balance

What error are you getting?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

xenophilia SSCommitted Points: 1891 More actions · Answer 2

Evil Kraig,

That works well. I don't detect extra rows or anything.

I am making things complicated withthe Over and Partition By, I can see.

Need to makes sense and be able to properly know when to use/not use:

OVER

PARTITION

ROWCOUNT

COUNT

RANK

and all aggregate functions.

Going on a trip to figure it out...

Thanks.

-

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

aitchkcandoo (1/22/2012)
Evil Kraig,
That works well. I don't detect extra rows or anything.
I am making things complicated withthe Over and Partition By, I can see.

By extra rows I meant there were rows not in your original desired result set. Glad it worked.

I personally would probably wait for Denali for the extra components of the windowing functions. They appear to make more sense and have had some bugs cleaned up.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

xenophilia SSCommitted Points: 1891 More actions · Answer 4

"Windowing" is a new term for me.

http://beyondrelational.com/blogs/nakul/archive/2011/03/14/windowing-functions-underappreciated-features-of-microsoft-sql-server.aspx

Thanks again, Evil Kraig.

-

xenophilia SSCommitted Points: 1891 More actions · Answer 5

One more. Again, what do I need to tweak here so that inv_number appears only once?

select inv_number, round(SUM(l.line_units * p.p_price) over (partition by inv_number), 2) as "Invoice Total"

from line l

join product p

on l.p_code = p.p_code

group by inv_number, l.line_units, p.p_price;

---above query results as follows

inv_numberInvoice Total

100124.94

10029.98

1003153.85

100434.87

100570.44

1006397.83

---DDL statements to recreate tables

/* -- */

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');

/* -- */

CREATE TABLE PRODUCT (

P_CODE varchar(10),

P_DESCRIPT varchar(35),

P_INDATE datetime,

P_QOH int,

P_MIN int,

P_PRICE float(8),

P_DISCOUNT float(8),

V_CODE int

);

INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','11/3/2007','8','5','109.98999786377','0','25595');

INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','12/13/2007','32','15','14.9899997711182','0.05','21344');

INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','11/13/2007','18','12','17.4899997711182','0','21344');

INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','1/15/2008','15','8','39.9500007629395','0','23119');

INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','1/15/2008','23','5','43.9900016784668','0','23119');

INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','12/30/2007','8','5','109.919998168945','0.05','24288');

INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','12/24/2007','6','5','99.870002746582','0.05','24288');

INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','1/20/2008','12','5','38.9500007629395','0.05','25595');

INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','1/20/2008','23','10','9.94999980926514','0.1','21225');

INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','1/2/2008','8','5','14.3999996185303','0.05','');

INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','12/15/2007','43','20','4.98999977111816','0','21344');

INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2/7/2008','11','5','256.989990234375','0.05','24288');

INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2/20/2008','188','75','5.86999988555908','0','');

INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25','3/1/2008','172','75','6.98999977111816','0','21225');

INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2/24/2008','237','100','8.44999980926514','0','21231');

INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','1/17/2008','18','5','119.949996948242','0.1','25595');

/* -- */

-

xenophilia SSCommitted Points: 1891 More actions · Answer 6

adding distinct, helped.

---as follows

select distinct inv_number, round(SUM(l.line_units * p.p_price) over (partition by inv_number), 2) as "Invoice Total"

from line l

join product p

on l.p_code = p.p_code

group by inv_number, l.line_units, p.p_price;

But, why doesn't partition over return just one inv_number per aggregate?

-

Paul White SSC Guru Points: 150467 More actions · Answer 7

Don't just add DISTINCT to remove the duplicates. There is a crucial difference between grouped aggregates (SUM...GROUP BY) and windowed aggregates (SUM OVER(PARTITION BY...)):

DECLARE @test-2 TABLE

(

c1 integer NOT NULL,

c2 integer NOT NULL

);

INSERT @test-2

(c1, c2)

VALUES

(1, 110),

(1, 120),

(2, 200),

(3, 300),

(3, 300);

-- Grouped aggregate

SELECT

t.c1,

SUM(t.c2) AS sum_c2

FROM @test-2 AS t

GROUP BY

t.c1

ORDER BY

t.c1;

-- Windowed aggregate

SELECT

t.c1,

SUM(t.c2) OVER (

PARTITION BY t.c1) AS sum_c2

FROM @test-2 AS t

ORDER BY

t.c1;

Result:

In the GROUP BY case, there is one SUM per group in the GROUP BY. In the windowed case, each row in the window of rows defined by the PARTITION BY clause gets the SUM value for all rows in the same window. They are two different things, useful in different cases. Here, you need the GROUP BY form Craig showed you, not a windowing aggregate.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

xenophilia SSCommitted Points: 1891 More actions · Answer 8

Paul,

Am I not to use group by and over(partition by) together?

Unfortunately, I don't see the answer, myself, yet.

---took away the edit and over(partition by)

select inv_number, round(SUM(l.line_units * p.p_price)as "Invoice Total"

from line l

join product p

on l.p_code = p.p_code

group by inv_number, l.line_units, p.p_price;

---results (partial)

inv_numberInvoice Total

10019.94999980926514

100114.9899997711182

10029.97999954223633

100338.9500007629395

100339.9500007629395

100374.9499969482422

100419.8999996185303

100414.9699993133545

100570.4400024414063

10069.94999980926514

1006109.919998168945

1006256.989990234375

-

xenophilia SSCommitted Points: 1891 More actions · Answer 9

Pardon, I mean this query

select inv_number, round(SUM(l.line_units * p.p_price), 2)as "Invoice Total"

from line l

join product p

on l.p_code = p.p_code

group by inv_number, l.line_units, p.p_price;

---results

inv_numberInvoice Total

10019.95

100114.99

10029.98

100338.95

100339.95

100374.95

100419.9

100414.97

100570.44

10069.95

1006109.92

1006256.99

How to change it so that it will return only one inv_number with amounts summed up?

-

xenophilia SSCommitted Points: 1891 More actions · Answer 10

Well, now it is working. It isn't that I hadn't tried grouping only by inv_number before, but that before I was getting an error unless I included the columns that were part of the aggregate function. It must be that it this was due to having the over(partition by) clause as part of the statement.

Go to bed and walk up 5 hours later, and voila, what was staring me in the face becomes clear.

---

select inv_number, round(SUM(line_units * line_price), 2)as "Invoice Total"

from line l

group by inv_number;

---

100124.94

10029.98

1003153.85

100434.87

100570.44

1006397.83

100734.97

1008399.15

-

jeetendrajaiswal2000 Valued Member Points: 73 More actions · Answer 11

Change your query to look like this.

select inv_number, round(SUM(l.line_units * p.p_price), 2) as "Invoice Total"

from line l

join product p

on l.p_code = p.p_code

group by inv_number;

jeetendrajaiswal2000 Valued Member Points: 73 More actions · Answer 12

Change your query to look like this

select inv_number, round(SUM(l.line_units * p.p_price), 2) as "Invoice Total"

from line l

join product p

on l.p_code = p.p_code

group by inv_number

xenophilia SSCommitted Points: 1891 More actions · Answer 13

OK, this is accurate. For the forum I left it with line table because the line_prices and the p_prices are the same and I didn't want to provide more DDL statments. But, thanks, I understand.

-