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
Solved

SQL Select statement that adds duplicates together as one

Posted on 2011-03-25
13
254 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

790 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