Solved

SQL Select statement that adds duplicates together as one

Posted on 2011-03-25
13
238 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

867 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

21 Experts available now in Live!

Get 1:1 Help Now