SQL Select statement that adds duplicates together as one

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?
amgrobinsAsked:
Who is Participating?
 
davehilditchConnect With a Mentor Commented:
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
 
davehilditchCommented:
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
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:

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

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
forgot to write "select" :P
0
 
amgrobinsAuthor Commented:
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
 
RGBDartCommented:
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
 
davehilditchCommented:
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
 
amgrobinsAuthor Commented:
thanks, I do need those other columns unfortunately.
0
 
davehilditchCommented:
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
 
amgrobinsAuthor Commented:
just the first thanks
0
 
davehilditchCommented:
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
 
davehilditchCommented:
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
 
amgrobinsAuthor Commented:
thanks. this worked :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.