January 23, 2017 at 7:38 am
I have three stocking locations. Two are within the same physical building but considered separate for other reasons. I want an inventory value to be grouped into one for that location.
CREATE TABLE test_inv_value (location_id varchar(10), inv_value decimal(9,2))
INSERT INTO test_inv_value
VALUES (1, 1000), (1,5000), (2,4000), (2,1500), (3,3000), (3,6000)
SELECT CASE WHEN location_id = 1 THEN 'Main'
WHEN location_id IN (2,3) THEN 'Warehouse' END AS 'Location'
, SUM(inv_value)
FROM test_inv_value
GROUP BY location_id
I have to have a GROUP BY because I'm summing. Because of this, when I run my SELECT, I get three lines, one for Main and two for Warehouse. I'd prefer to see two lines, one for main and one for Warehouse that shows the inventory value for locations 2 and 3 together. I guess I could do a CTE first, but I'd rather do it in one script if that's possible. What I'd like to see is this.Location Inv_value
Main 6000
Warehouse 14500
January 23, 2017 at 7:46 am
jcobb 20350 - Monday, January 23, 2017 7:38 AMI have three stocking locations. Two are within the same physical building but considered separate for other reasons. I want an inventory value to be grouped into one for that location.CREATE TABLE test_inv_value (location_id varchar(10), inv_value decimal(9,2))
INSERT INTO test_inv_value
VALUES (1, 1000), (1,5000), (2,4000), (2,1500), (3,3000), (3,6000)
SELECT CASE WHEN location_id = 1 THEN 'Main'
WHEN location_id IN (2,3) THEN 'Warehouse' END AS 'Location'
, SUM(inv_value)
FROM test_inv_value
GROUP BY location_id
I have to have a GROUP BY because I'm summing. Because of this, when I run my SELECT, I get three lines, one for Main and two for Warehouse. I'd prefer to see two lines, one for main and one for Warehouse that shows the inventory value for locations 2 and 3 together. I guess I could do a CTE first, but I'd rather do it in one script if that's possible. What I'd like to see is this.Location Inv_value
Main 6000
Warehouse 14500
You're nearly there. Just repeat the whole CASE expression in your GROUP BY clause. If that looks too untidy, wrap your query as posted into a CTE, and select from that, grouping by Location.
John
January 23, 2017 at 7:59 am
John Mitchell-245523 - Monday, January 23, 2017 7:46 AMjcobb 20350 - Monday, January 23, 2017 7:38 AMI have three stocking locations. Two are within the same physical building but considered separate for other reasons. I want an inventory value to be grouped into one for that location.CREATE TABLE test_inv_value (location_id varchar(10), inv_value decimal(9,2))
INSERT INTO test_inv_value
VALUES (1, 1000), (1,5000), (2,4000), (2,1500), (3,3000), (3,6000)
SELECT CASE WHEN location_id = 1 THEN 'Main'
WHEN location_id IN (2,3) THEN 'Warehouse' END AS 'Location'
, SUM(inv_value)
FROM test_inv_value
GROUP BY location_id
I have to have a GROUP BY because I'm summing. Because of this, when I run my SELECT, I get three lines, one for Main and two for Warehouse. I'd prefer to see two lines, one for main and one for Warehouse that shows the inventory value for locations 2 and 3 together. I guess I could do a CTE first, but I'd rather do it in one script if that's possible. What I'd like to see is this.Location Inv_value
Main 6000
Warehouse 14500You're nearly there. Just repeat the whole CASE expression in your GROUP BY clause. If that looks too untidy, wrap your query as posted into a CTE, and select from that, grouping by Location.
John
So simple. Thanks John. Here's what I wound up with. I learned something new today.
GROUP BY CASE WHEN location_id = 1 THEN 'Main' WHENlocation_id IN (2,3) THEN 'Warehouse' END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply