Solved

SQL Select statement that adds duplicates together as one

Posted on 2011-03-25
13
228 Views
Last Modified: 2012-08-14
Hello,

I have the following table called: LineItems

Id, POId, Description, Qty, Price, TotalPrice, JobNo, Status

Example data may be:

1, 1, 2 3/4" Bolt, 10, 0.39, 3.90, 4454, Pending
2, 1, 2 Inch Spanner, 4, 9.00, 36.00, 4780, Pending
3, 1, 5m Sandscreen, 4, 5.00, 20.00, 4410, Pending
4, 1, 5m Sandscreen, 8, 5.00, 40.00, 4411, Pending
5, 1, 5m Sandscreen, 5, 5.00, 25.00, 4412, Pending

When i present my data I would like to only have 1 entry for the 5m Sandscreen but fill the qty column with the combined total e.g. 17 along with the total price column.

is this possible?
0
Comment
Question by:amgrobins
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 4

Expert Comment

by:davehilditch
ID: 35214093
yes, use a group by query, e.g

select description, sum(quantity) as totalquantity, sum(totalprice) as totalprice
from LineItems
group by description

you can also use a CTE if you need access to other ungrouped columns such as the itemids.
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 35214099

Id, POId, Description, SUM(Qty), Price, TotalPrice, JobNo, Status from LineItems GROUP BY Description

Open in new window

0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 35214104
forgot to write "select" :P
0
 

Author Comment

by:amgrobins
ID: 35214115
using the query builder a tried this;

SELECT        Id, poId, sum(quantity), description, partNo, unitPrice, sum(totalPrice), Status, partsId, supplierId
FROM            tempPoLineItems
where poId=@UserId
group by description

But i get the following error when trying to apply my changes;

Column 'tempPoLineItems.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 4

Expert Comment

by:RGBDart
ID: 35214126
davehilditch wrote correct query, while rikin_shah - didn't (even "select" won't help you - you must group by all fields, that no aggregated)
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 35214143
if you need access to the other columns it's a lot more complicated - give me a little bit of time and I'll try and write a common table expression to do the job for you
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:amgrobins
ID: 35214145
thanks, I do need those other columns unfortunately.
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 35214247
Can you tell me what you want to appear in the ID, Pold, PartNo, Status, ParsID, Supplier columns?

e.g. for the id column you have the ids 3, 4 and 5 for 5m Sandscreen.  You want just the first?  or do you want a comma separated list?
0
 

Author Comment

by:amgrobins
ID: 35214295
just the first thanks
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 35214349
Ok - here's some code that i've created with just one column - the id column - just add the rest in to the CTE and this will work for you now:


create table dbo.tbParts (id int, [desc] varchar(10), quantity int, totalprice decimal)
insert into dbo.tbParts select 1, 'item a', 5, 10
insert into dbo.tbParts select 2, 'item b', 2, 10
insert into dbo.tbParts select 3, 'item b', 4, 20
insert into dbo.tbParts select 4, 'item b', 8, 40

select [desc], sum(quantity), sum(totalprice)
from dbo.tbparts
group by [desc]

with partsaggregation as (
      select id, [desc],
      sum(quantity) over(partition by [desc]) totalquantity,
      sum(totalprice) over(partition by [desc]) totalprice,
      row_number() over(partition by [desc] order by id) rownum
      from dbo.tbparts
)
select * from partsaggregation
where rownum = 1
0
 
LVL 4

Accepted Solution

by:
davehilditch earned 500 total points
ID: 35214365
i.e. this code should work.  For your understanding of the code, try running it without the 'where rownum = 1' and lookup CTEs.  Itzik Ben Gan has written excellent articles on these.

with lineitemsaggregation as (
      select Id, POId, Description, Price, JobNo, Status,
      sum(qty) over(partition by Description) totalquantity,
      sum(totalprice) over(partition by Description) totalprice,
      row_number() over(partition by Description order by id) rownum
      from lineitems
)
select * from lineitemsaggregation
where rownum = 1
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 35214384
Incidentally, if you DID want to get a comma separated list of the aggregated items the easiest way would be to write a CLR aggregate function which simply concatenates the items with a comma.  It would be nice if MS had included a function like that by default but then on the other hand I'm sure it would lead many people down some dodgy programming practice paths!
0
 

Author Closing Comment

by:amgrobins
ID: 35334848
thanks. this worked :)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now