May 28, 2019 at 4:40 pm
I have a table that has a shipping quantity (tot_ship) in all lines for a given user. I want the want the duplicates removed for the users and only keep one shipping quantity. I just cant think of how to accomplish this right off hand.
User qty tot_ship
Joe 25 30.00
Joe 15 30.00
Joe 10 30.00
Dan 15 50.00
Dan 10 50.00
Lori 45 75.00
User qty tot_ship
Joe 25 30.00
Joe 15
Joe 10
Dan 15 50.00
Dan 10
Lori 45 75.00
May 28, 2019 at 6:18 pm
I see you have posted the original resultset, however, so we know for sure what the goal is, please post a sample of what the resultset should look like after removing duplicates.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 28, 2019 at 7:47 pm
It's not clear what you are asking for. The result set you post, suggests that you want to blank out tot_ship for rows but one. This can be done in a query, but it's nothing you do normally. Tot_ship is obviously a decimal value, and blank is a string, and you cannot mix data types in a result. This makes the operation painful. The normal approach is to handle this in your reporting tool, as this is a presentation issue. The purpose of the database engine is to return data.
If you just want a single line per user and tot_ship with the total qty, it is
SELECT User, tot_ship, SUM(qty)
FROM tbl
GROUP BY User, tot_ship
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 28, 2019 at 9:35 pm
This is something that is usually best left to the presentation layer. You can do it in SQL, but it is much easier in SQL 2012. Are you really still using SQL 2008?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply