Add calculated row item - SQL 2008

Question

Post reply

Add calculated row item - SQL 2008

helal.mobasher 13209

SSCommitted

Points: 1526
More actions
March 12, 2015 at 4:09 pm

#305755

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

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

helal.mobasher 13209 SSCommitted Points: 1526 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Can you give an example?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

helal.mobasher 13209 SSCommitted Points: 1526 More actions · Answer 4

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

How would you expect to differentiate both product groups?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

helal.mobasher 13209 SSCommitted Points: 1526 More actions · Answer 6

I am sorry but I am not following your question! the issue is to add more rows with the same logic as O.

Helal

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

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?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

helal.mobasher 13209 SSCommitted Points: 1526 More actions · Answer 8

helal.mobasher 13209

SSCommitted

Points: 1526

March 17, 2015 at 11:18 am

#1784557

Oh that, I know that for sure since they are unique.

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 9

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 10

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?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

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;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Add calculated row item - SQL 2008

Cookies on SQLServerCentral