Adding specific rows together in query

Question

I am trying to write a query that displays the total value sold of items in a product line. For some of these items, only one row is needed to return the answer. For others, two or more rows need to be added together to get the correct value. The issue is that the Item ID's for some of the items do not indicate that they should go together (Item ID 1030, 1031, 5032, and 1033 may go together). There is no column that I can group these on, so I figure I will have to do it by hand.

Is there anyway to add together specific values in the same column?

Here is a small demonstration of what I am talking about. In the first column I have the ID of the item, and the second column contains the value. I need to add the value together for rows that are in the same product line. Unfortunately, the Item ID is only similar sometimes, but other times the ID's do not fit with the others.

https://i.sstatic.net/gh4vg.jpg

Welcome to SO. What I think you need is supporting tables containing product details where known. You could then use GROUP BY together with LEFT JOIN to get the final result. You can also use ISNULL to set a suitable default for those products where you don't product details. Do you have some sample data/table design? — Peter Smith
– Peter Smith, Commented Jul 29, 2019 at 19:12
Sure this is possible. How do you know which product line a given ItemID belongs to? At the very least you need a way to determine that. Also, when posting questions be careful about which DBMS you tag as mysql and sql server are very different. — Sean Lange
– Sean Lange, Commented Jul 29, 2019 at 19:12
I only know which ItemID goes to which product line because I was given a handwritten paper that lists this information that the guy who had this job before me figured out. So unfortunately there is no table that can provide more information. I could see if IT will let me create a new table. — Ironicallylaughing
– Ironicallylaughing, Commented Jul 29, 2019 at 19:25

Max Szczurek · Accepted Answer · 2019-07-29 19:47:42Z

1

You should add a 2nd table relating product lines to items, join it to your sales table, and do a SUM on the [Value of Sold] column. Here's a SQL Server example, along with SQL Server and MySQL fiddles.

CREATE TABLE ProductLines ([Item ID] INT, [Product Line #] INT);
INSERT INTO ProductLines VALUES 
(1010, 1),
(1011, 1),
(1012, 1),
(1020, 2),
(1030, 3),
(1031, 3),
(5032, 3),
(1033, 3),
(1040, 4),
(7041, 4),
(2040, 1);

SELECT [ProductLines].[Product Line #], SUM([Value of Sold]) AS [Total Sold]
FROM dbo.Sales 
INNER JOIN ProductLines ON [ProductLines].[Item ID] = Sales.[Item ID]
GROUP BY [Product Line #]

Returns:

Product Line # Total Sold
-------------- -----------
1              54
2              9
3              42
4              10

Edit: Since you don't have permission to create a table, see if you can create a table variable instead of persisting a table:

DECLARE @ProductLines TABLE ([Item ID] INT, [Product Line #] INT)
INSERT INTO @ProductLines VALUES 
(1010, 1),
(1011, 1),
(1012, 1),
(1020, 2),
(1030, 3),
(1031, 3),
(5032, 3),
(1033, 3),
(1040, 4),
(7041, 4),
(2040, 1);

SELECT ProductLines.[Product Line #], SUM([Value of Sold]) AS [Total Sold]
FROM dbo.Sales 
INNER JOIN @ProductLines ProductLines ON ProductLines.[Item ID] = Sales.[Item ID]
GROUP BY [Product Line #]

edited Jul 29, 2019 at 19:47

answered Jul 29, 2019 at 19:16

Max Szczurek

4,3342 gold badges23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ironicallylaughing Over a year ago

I definitely agree that there should be a 2nd table that displays what items go to which product lines, however I do not currently have a high enough level of permission to add a table. But what you answered should be able to answer my question until I am able to add the 2nd table. Thank you!

Max Szczurek Over a year ago

@Ironicallylaughing See edit - you may be able to work around it by creating a table variable instead of creating a table.

Max Szczurek Over a year ago

@Ironicallylaughing Did this help you?

Ironicallylaughing Over a year ago

Yes it definitely did, set me on the right path for sure.

Peter Smith · Accepted Answer · 2019-07-29 19:20:24Z

0

Assuming a table called sales with columns 'SaleID, 'ItemID' andValueOfSaleand another table called 'ProductDetails with columns ItemID and ProductDescription then something like might solve your problem.

SELECT
   ItemID, 
   SUM(ValueOfSale),
   ISNULL(ProductDescription, '') AS [Product Description]
FROM
   sales AS SA 
   LEFT JOIN ProductDetails AS PD
    ON SA.ItemID = PD.ItemID
GROUP BY
    ItemID, ProductDescription

This is NOT tested.

answered Jul 29, 2019 at 19:20

Peter Smith

5,5529 gold badges54 silver badges80 bronze badges

4 Comments

Ironicallylaughing Over a year ago

Thank you for this. I'm sure that if I had the second table that something very similar to this would work.

Peter Smith Over a year ago

So, please show us what your source data/tables are.

Ironicallylaughing Over a year ago

Here is essentially what the table looks like. There are quite a few binary variables after this that no one is sure what they mean imgur.com/k0903wA

Peter Smith Over a year ago

I don't see how, with so little information/help/support you can solve this. Are you able to say this?

Collectives™ on Stack Overflow

Adding specific rows together in query

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related