Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Select statement that adds duplicates together as one

Posted on 2011-03-25
13
Medium Priority
?
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 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