March 12, 2015 at 4:09 pm
I need to add a calculated column item in the same column. Please see SQL Codes for both existing data and desired outcome.
Product O is added according to:
for 201501 Product O= sum of en_count for product Y,W,N when yrmnth=201501
for 201502 Product O= sum of sum of en_count for product Y,W,N when yrmnth=20150
Thanks,
Helal
SQL:
--Existing Data
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
product char(100),
yrmnth varchar(6),
en_count int,
date date,
)
INSERT INTO #Table1
(product, yrmnth, en_count,date)
SELECT 'Y', '201501', 5000 , '01/01/2015' union all
SELECT 'Y', '201502', 6000 , '02/01/2015' union all
SELECT 'Z', '201501', 7000 , '01/01/2015' union all
SELECT 'Z', '201502', 8000 , '02/01/2015' union all
SELECT 'W', '201501', 9000 , '01/01/2015' union all
SELECT 'W', '201502', 10000 , '02/01/2015' union all
SELECT 'N', '201501', 11000 , '01/01/2015' union all
SELECT 'N', '201502', 12000 , '02/01/2015'
--Desired Outcome
IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2
--===== Create the test table with
CREATE TABLE #Table2
(
product char(100),
yrmnth varchar(6),
en_count int,
date date,
)
INSERT INTO #Table2
(product, yrmnth, en_count,date)
SELECT 'Y', '201501', 5000 , '01/01/2015' union all
SELECT 'Y', '201502', 6000 , '02/01/2015' union all
SELECT 'Z', '201501', 7000 , '01/01/2015' union all
SELECT 'Z', '201502', 8000 , '02/01/2015' union all
SELECT 'W', '201501', 9000 , '01/01/2015' union all
SELECT 'W', '201502', 10000 , '02/01/2015' union all
SELECT 'N', '201501', 11000 , '01/01/2015' union all
SELECT 'N', '201502', 12000 , '02/01/2015' union all
SELECT 'O', '201501', 32000 , '01/01/2015' union all
SELECT 'O', '201502', 36000 , '02/01/2015'
select *
from #Table2
March 12, 2015 at 4:38 pm
You have 2 options:
1. Use a UNION ALL with one query including a detail of the products and another one aggregating the information.
2. Use GROUPING SETS
SELECT ISNULL( product, 'O') product,
yrmnth,
SUM(en_count) en_count,
date
FROM #Table1
GROUP BY GROUPING SETS((product,yrmnth, date),(yrmnth, date));
March 12, 2015 at 5:06 pm
Grouping Sets worked great. Thank you...can I add more items besides O with Grouping Sets? if yes, can you give me an example please?
Helal
March 12, 2015 at 5:31 pm
March 13, 2015 at 9:13 am
Just like adding O, now I add C with the same logic. So C for 2010501 will be the sum of (a201501 and b201501). Here are data:
--Existing Data
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
product char(100),
yrmnth varchar(6),
en_count int,
date date,
)
INSERT INTO #Table1
(product, yrmnth, en_count,date)
SELECT 'Y','201501',5000, '01/01/2015' union all
SELECT 'Y','201502',6000, '02/01/2015' union all
SELECT 'Z','201501',7000, '01/01/2015' union all
SELECT 'Z','201502',8000, '02/01/2015' union all
SELECT 'W','201501',9000, '01/01/2015' union all
SELECT 'W','201502',10000 , '02/01/2015' union all
SELECT 'N','201501',11000 , '01/01/2015' union all
SELECT 'N','201502',12000 , '02/01/2015' union all
SELECT 'A','201501',13000 , '01/01/2015' union all
SELECT 'A','201502',14000 , '02/01/2015' union all
SELECT 'B','201501',15000 , '01/01/2015' union all
SELECT 'B','201502',16000 , '02/01/2015'
--Desired Outcome
IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2
--===== Create the test table with
CREATE TABLE #Table2
(
product char(100),
yrmnth varchar(6),
en_count int,
date date,
)
INSERT INTO #Table2
(product, yrmnth, en_count,date)
SELECT 'Y','201501',5000 , '01/01/2015' union all
SELECT 'Y','201502',6000 , '02/01/2015' union all
SELECT 'Z','201501',7000 , '01/01/2015' union all
SELECT 'Z','201502',8000 , '02/01/2015' union all
SELECT 'W','201501',9000 , '01/01/2015' union all
SELECT 'W','201502',10000 , '02/01/2015' union all
SELECT 'N','201501',11000 , '01/01/2015' union all
SELECT 'N','201502',12000 , '02/01/2015' union all
SELECT 'O','201501',32000 , '01/01/2015' union all
SELECT 'O','201502',36000 , '02/01/2015' union all
SELECT 'A','201501',13000 , '01/01/2015' union all
SELECT 'A','201502',14000 , '02/01/2015' union all
SELECT 'B','201501',15000 , '01/01/2015' union all
SELECT 'B','201502',16000 , '02/01/2015' union all
SELECT 'C','201501',28000 , '01/01/2015' union all
SELECT 'C','201502',31000 , '02/01/2015'
select *
from #Table2
Thank You,
Helal
March 13, 2015 at 7:29 pm
How would you expect to differentiate both product groups?
March 16, 2015 at 8:14 am
I am sorry but I am not following your question! the issue is to add more rows with the same logic as O.
Helal
March 17, 2015 at 11:06 am
How do you you know that C is the sum of A and B and not any of the other values? How do you know that O shouldn't sum the values from A and B? What's the logic behind this?
March 17, 2015 at 11:18 am
Oh that, I know that for sure since they are unique.
March 17, 2015 at 1:24 pm
Quick suggestion, group and aggregate the set separately and then union the two sets
😎
USE tempdb;
GO
SET NOCOUNT ON;
--Existing Data
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
product char(100),
yrmnth varchar(6),
en_count int,
date date,
)
INSERT INTO #Table1
(product, yrmnth, en_count,date)
SELECT 'Y','201501',5000, '01/01/2015' union all
SELECT 'Y','201502',6000, '02/01/2015' union all
SELECT 'Z','201501',7000, '01/01/2015' union all
SELECT 'Z','201502',8000, '02/01/2015' union all
SELECT 'W','201501',9000, '01/01/2015' union all
SELECT 'W','201502',10000 , '02/01/2015' union all
SELECT 'N','201501',11000 , '01/01/2015' union all
SELECT 'N','201502',12000 , '02/01/2015' union all
SELECT 'A','201501',13000 , '01/01/2015' union all
SELECT 'A','201502',14000 , '02/01/2015' union all
SELECT 'B','201501',15000 , '01/01/2015' union all
SELECT 'B','201502',16000 , '02/01/2015'
--Desired Outcome
;WITH O_DATA AS
(
SELECT
'O' AS product
,SD.yrmnth
,SUM(SD.en_count) AS en_count
,SD.[date]
FROM #Table1 SD
WHERE SD.product IN ('A','B')
GROUP BY SD.yrmnth
,SD.[date]
)
SELECT
SD.product
,SD.yrmnth
,SD.en_count
,SD.[date]
FROM #Table1 SD
UNION ALL
SELECT
OD.product
,OD.yrmnth
,OD.en_count
,OD.[date]
FROM O_DATA OD
;
Results
product yrmnth en_count date
--------- ------ ----------- ----------
Y 201501 5000 2015-01-01
Y 201502 6000 2015-02-01
Z 201501 7000 2015-01-01
Z 201502 8000 2015-02-01
W 201501 9000 2015-01-01
W 201502 10000 2015-02-01
N 201501 11000 2015-01-01
N 201502 12000 2015-02-01
A 201501 13000 2015-01-01
A 201502 14000 2015-02-01
B 201501 15000 2015-01-01
B 201502 16000 2015-02-01
O 201501 28000 2015-01-01
O 201502 30000 2015-02-01
Edit: Changed to the later data sample
March 17, 2015 at 1:39 pm
The desired results do not match the sample data + the logic, can you elaborate on this?
1) What is the definition of 'O'?
2) Why the difference between the requirements and the desired results?
😎
March 17, 2015 at 4:03 pm
This is what I thought, but I'm not sure if it helps because I still don't know the logic to differentiate one group from the other.
CREATE TABLE #Products
(
product char(100),
category char(100))
INSERT INTO #Products
SELECT 'Y','O' union all
SELECT 'Z','O' union all
SELECT 'W','O' union all
SELECT 'N','O' union all
SELECT 'A','C' union all
SELECT 'B','C'
SELECT ISNULL( t.product, p.category) product,
yrmnth,
SUM(en_count) en_count,
date
FROM #Table1 t
JOIN #Products p ON t.product = p.product
GROUP BY GROUPING SETS((p.category, t.product,yrmnth, date),(p.category, yrmnth, date))
ORDER BY p.category DESC, ISNULL( t.product, 'ZZZZZZZ'), yrmnth;
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply