Calculation in group by query.

Question

Calculation in group by query.

Pravin Patel-491467

Say Hey Kid

Points: 674
More actions
May 24, 2010 at 4:55 am

#219200

Hi Friends,
I have one result set which is as below.
--------------------Run below script in your query window.--------------------
GO
CREATE TABLE [dbo].[Child](
[Order_No] [int] NULL,
[Party] [varchar](50) NULL,
[Shipped_Cnt] [int] NULL
) ON [PRIMARY]
GO
Insert into [master] values (10,'Pravin',1)
Insert into [master] values (6,'Mitesh',2)
Insert into [master] values (8,'Siddhi',4)
GO
CREATE TABLE [dbo].[Master](
[Cnt] [int] NULL,
[Name] [varchar](10) NULL,
[Order_No] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
Insert into child values (1,'Nitin',4)
Insert into child values (1,'Shivang',3)
Insert into child values (1,'Maulik',3)
Insert into child values (2,'Mitul',2)
Insert into child values (2,'Ashish',3)
Insert into child values (2,'Kaushik',1)
Insert into child values (4,'Dhaval',2)
Insert into child values (4,'Navin',4)
Insert into child values (4,'Swati',1)
Insert into child values (4,'Varsha',1)
GO
SELECT 'Order_No' = CASE WHEN A.New = 1 THEN CONVERT(VARCHAR(20),A.Order_No) ELSE ' ' END,
'Name' = CASE WHEN A.New = 1 THEN A.Name ELSE '' END,
'Total_Cnt' = CASE WHEN A.New = 1 THEN CONVERT(VARCHAR(20),A.Cnt) ELSE ' ' END,
A.Party, A.Shipped_Cnt, 'Pnd_Cnt' = A.Cnt - A.Shipped_Cnt FROM
(select M.Order_No,M.Name,M.Cnt,C.Party,C.Shipped_Cnt, 'New' = ROW_NUMBER() over (partition by Cnt order by Cnt)
from Master M
INNER JOIN Child C ON M.Order_No = C.Order_No) A
Order BY A.Order_No
-------------------------------------------------------------------------------
In above query's result set last column "Pnd_Cnt" i want remaining container.
Ex. For Order_Id 1. Total container = 10. Now in first row i shipped 4 container to "Nitin". So, in pending container it should be 6 (10 - 4) which i got. But in second row, for same order i shipped 3 container to "Shivang". now, pending container should be 3 (10 - (4 + 3)). and same time first row pending container should be 0.
I appreciate if anyone knows this calculation.
Thansk - Pravin.

Viewing 8 posts - 1 through 7 (of 7 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You might want to have a look at Jeff's "Running Total" article. You'll find several solutions including a comparison. Depending on the number of rows you're dealing with you might get away with the triangular join.... But as soon as you need to deal with a larger data volume you probably should look into the "quirky update" method.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

@Privan,

Thanks for the nice data setup. I'd jump right in on the code except I'm at work and won't be able to work on this until later tonight. In the meantime, Lutz is correct.. the "Quirky" update will do the trick in a very high speed fashion and you should look at the article he linked up. Be sure to follow the rules of usage near the end of the article or things could mess up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pravin Patel-491467 Say Hey Kid Points: 674 More actions · Answer 3

I think this is work...

SELECT

'Order_No' = CASE WHEN C.Row_No = 1 THEN CONVERT(VARCHAR(20),C.Order_No) ELSE ' ' END,

'Name' = CASE WHEN C.Row_No = 1 THEN C.Name ELSE '' END,

'Total_Cnt' = CASE WHEN C.Row_No = 1 THEN CONVERT(VARCHAR(20),C.Cnt) ELSE ' ' END,

C.Party, C.Shipped_Cnt, C.Row_No,

'Pend_Cont' = CASE WHEN C.Row_No = 1 THEN C.Pend_Cont ELSE 0 END

--C.Pend_Cont

FROM(

select M.Order_No,M.Name,M.Cnt,C.Party,C.Shipped_Cnt, 'Pend_Cont' = M.Cnt - B.Sum_Cnt,

'Row_No' = ROW_NUMBER() OVER (Partition by M.order_no order by C.Party DESC)

from [Master] M INNER JOIN Child C ON M.Order_No = C.Order_No

LEFT OUTER JOIN

(select M1.Order_No,'Sum_Cnt' = SUM(C1.Shipped_Cnt)

from [Master] M1 INNER JOIN Child C1 ON M1.Order_No = C1.Order_No

GROUP BY M1.Order_No) B

ON M.Order_No = B.Order_No

) C

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Pravin Patel-491467 (5/24/2010)
Hi Friends,
I have one result set which is as below.
--------------------Run below script in your query window.--------------------
GO
CREATE TABLE [dbo].[Child](
[Order_No] [int] NULL,
[Party] [varchar](50) NULL,
[Shipped_Cnt] [int] NULL
) ON [PRIMARY]
GO
Insert into [master] values (10,'Pravin',1)
Insert into [master] values (6,'Mitesh',2)
Insert into [master] values (8,'Siddhi',4)
GO
CREATE TABLE [dbo].[Master](
[Cnt] [int] NULL,
[Name] [varchar](10) NULL,
[Order_No] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
Insert into child values (1,'Nitin',4)
Insert into child values (1,'Shivang',3)
Insert into child values (1,'Maulik',3)
Insert into child values (2,'Mitul',2)
Insert into child values (2,'Ashish',3)
Insert into child values (2,'Kaushik',1)
Insert into child values (4,'Dhaval',2)
Insert into child values (4,'Navin',4)
Insert into child values (4,'Swati',1)
Insert into child values (4,'Varsha',1)
GO
SELECT 'Order_No' = CASE WHEN A.New = 1 THEN CONVERT(VARCHAR(20),A.Order_No) ELSE ' ' END,
'Name' = CASE WHEN A.New = 1 THEN A.Name ELSE '' END,
'Total_Cnt' = CASE WHEN A.New = 1 THEN CONVERT(VARCHAR(20),A.Cnt) ELSE ' ' END,
A.Party, A.Shipped_Cnt, 'Pnd_Cnt' = A.Cnt - A.Shipped_Cnt FROM
(select M.Order_No,M.Name,M.Cnt,C.Party,C.Shipped_Cnt, 'New' = ROW_NUMBER() over (partition by Cnt order by Cnt)
from Master M
INNER JOIN Child C ON M.Order_No = C.Order_No) A
Order BY A.Order_No
-------------------------------------------------------------------------------
In above query's result set last column "Pnd_Cnt" i want remaining container.
Ex. For Order_Id 1. Total container = 10. Now in first row i shipped 4 container to "Nitin". So, in pending container it should be 6 (10 - 4) which i got. But in second row, for same order i shipped 3 container to "Shivang". now, pending container should be 3 (10 - (4 + 3)). and same time first row pending container should be 0.
I appreciate if anyone knows this calculation.
Thansk - Pravin.

The script doesn't work because there are INSERTs for table 'master' before it's created.

If the table is created before the INSERT's then it will fail because explicit values are specified for an identity column.

If IDENTITY_INSERT is set to ON to allow explicit values for column [Order_No], then the script will still fail because no column list has been specified!

DROP TABLE #Child

DROP TABLE #Master

CREATE TABLE #Child(

[Order_No] [int] NULL,

[Party] [varchar](50) NULL,

[Shipped_Cnt] [int] NULL

) ON [PRIMARY]

Insert into #child values (1,'Nitin',4)

Insert into #child values (1,'Shivang',3)

Insert into #child values (1,'Maulik',3)

Insert into #child values (2,'Mitul',2)

Insert into #child values (2,'Ashish',3)

Insert into #child values (2,'Kaushik',1)

Insert into #child values (4,'Dhaval',2)

Insert into #child values (4,'Navin',4)

Insert into #child values (4,'Swati',1)

Insert into #child values (4,'Varsha',1)

CREATE TABLE #Master(

[Cnt] [int] NULL,

[Name] [varchar](10) NULL,

[Order_No] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY]

SET IDENTITY_INSERT #Master ON

Insert into #Master (Cnt, Name, [Order_No]) values (10,'Pravin',1)

Insert into #Master (Cnt, Name, [Order_No]) values (6,'Mitesh',2)

Insert into #Master (Cnt, Name, [Order_No]) values (8,'Siddhi',4)

SET IDENTITY_INSERT #Master OFF

SELECT m.*,

'<' AS '<', -- boundary between the columns of the two tables

c.*,

'' AS RequiredResult -- what do you want to see in this column?

FROM #Master m

LEFT JOIN #Child c ON c.Order_No = m.Order_No

ORDER BY m.Order_No -- then what?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 5

try this query

SELECT A.Order_No AS 'Order_No',

'Name'= CASE WHEN A.New = 1 THEN A.Name ELSE '' END,

a.Cnt as Total_Cnt,

A.Party, A.Shipped_Cnt, 'Pnd_Cnt' = A.Cnt - A.Shipped_Cnt into #temp

FROM

(select M.Order_No,M.Name,M.Cnt,C.Party,C.Shipped_Cnt, 'New' = ROW_NUMBER() over (partition by Cnt order by Cnt)

from Master M

INNER JOIN Child C ON M.Order_No = C.Order_No) A

Order BY A.Order_No

DECLARE @val INT, @Order_No INT

SET @val=0

SET @Order_No=1

UPDATE#Temp

SET

@val=CASE WHEN @Order_No = Order_No THEN Shipped_Cnt+@val

WHEN (@Order_No <>Order_No)THEN Shipped_Cnt

-- WHEN @Order_No > 1 THEN Shipped_Cnt

ELSE Shipped_Cnt

END ,

@Order_No=Order_No,

Pnd_Cnt=Total_Cnt - @val

FROM #Temp

SELECT *

FROM #Temp A

Order BY A.Order_No

DROP TABLE #temp

Pravin Patel-491467 Say Hey Kid Points: 674 More actions · Answer 6

Friends,

Thanks a lot for all your help and giving your precious time behind this stuff.

I am posting one more stuff related this stuff in same forums TSQL(SS2K5). If possible please help me.

Pravin Patel.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

Pravin Patel-491467 (5/25/2010)
Friends,
Thanks a lot for all your help and giving your precious time behind this stuff.
I am posting one more stuff related this stuff in same forums TSQL(SS2K5). If possible please help me.
Pravin Patel.

Heh... so post the final solution you ended up with so we can learn what you did. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)