May 3, 2015 at 12:45 pm
ID A B C AVG
------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
Friends,
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column,
PLz help me out.......
Thank you.
May 3, 2015 at 12:49 pm
Uday3421 (5/3/2015)
ID A B C AVG------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
Friends,
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column,
PLz help me out.......
Thank you.
what are your expected results from this?
can you show us what you have tried so far?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 3, 2015 at 1:39 pm
ID A B C AVG
------------------------
1 08 09 10 10
------------------------
2 10 25 26 26
------------------------
3 09 15 16 16
------------------------
i trying to get the average of two largest number from the column a,b&c for the particular identity and store it into he average column...
Like,
for ID=1
the two largest numbers are 10 and 09 and the average of that two number is (10+9)/2=10.
(25+26)/2=26.
and
(15+16)/2=16.
the result should not come in the float number......
May 3, 2015 at 1:39 pm
Uday3421 (5/3/2015)
ID A B C AVG------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
Friends,
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column,
PLz help me out.......
Thank you.
Few ways of doing this, here is one
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TAVG TABLE
(
ID INT NOT NULL
,A NUMERIC(12,2) NOT NULL
,B NUMERIC(12,2) NOT NULL
,C NUMERIC(12,2) NOT NULL
,[AVG] INT NULL
);
INSERT INTO @TAVG(ID,A,B,C)
VALUES
(1,08.0,09.0,10.0)
,(2,10.0,25.0,26.0)
,(3,09.0,15.0,16.0);
;WITH BASE_DATA AS
(
SELECT
A.ID
,ROW_NUMBER() OVER
(
PARTITION BY A.ID
ORDER BY X.VAL DESC
) AS X_DRID
,X.VAL
FROM @TAVG A
CROSS APPLY
(
SELECT A.A UNION ALL
SELECT A.B UNION ALL
SELECT A.C
) AS X(VAL)
)
,AVG_DATA AS
(
SELECT
BD.ID
,AVG(BD.VAL) AS AVG_VAL
FROM BASE_DATA BD
WHERE BD.X_DRID < 3
GROUP BY BD.ID
)
UPDATE TA
SET TA.[AVG] = AD.AVG_VAL
FROM @TAVG TA
INNER JOIN AVG_DATA AD
ON TA.ID = AD.ID
;
SELECT
A.ID
,A.A
,A.B
,A.C
,A.[AVG]
FROM @TAVG A;
Results
ID A B C AVG
--- ------ ------------ -----
1 8.00 9.00 10.00 9
2 10.00 25.00 26.00 25
3 9.00 15.00 16.00 15
May 3, 2015 at 6:41 pm
Uday3421 (5/3/2015)
ID A B C AVG------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
Friends,
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column,
PLz help me out.......
Thank you.
There is no need for anything sophisticated here. In this case, brute force is the shortest and most likely the fastest. The "+1" thing in the code below is to meet your "round up" requirements without actually using a function. If the columns are of an integer datatype, then this is all integer math as you requested.
UPDATE #TestTable
SET [Avg] = CASE
WHEN A<B AND A<C THEN (B+C+1)/2
WHEN B<A AND B<C THEN (A+C+1)/2
ELSE (A+B+1)/2
END
;
If someone desires to check for performance, here's my normal million row test table.
--===== If the test table exists, drop it to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on the fly.
SELECT TOP 1000000
ID = IDENTITY(INT,1,1)
,A = ABS(CHECKSUM(NEWID()))%30
,B = ABS(CHECKSUM(NEWID()))%30
,C = ABS(CHECKSUM(NEWID()))%30
,[AVG] = CAST(0 AS INT)
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
And, of course, the following query demonstrates that the data provided by the OP is also calculated correctly according to the OP.
--===== Display the answers for given rows.
SELECT tt.*
FROM #TestTable tt
JOIN (
SELECT 08,09,10 UNION ALL
SELECT 10,25,26 UNION ALL
SELECT 09,15,16
)d(A,B,C)
ON tt.A = d.A
AND tt.B = d.B
AND tt.C = d.C
;
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2015 at 8:16 pm
Jeff Moden (5/3/2015)
Uday3421 (5/3/2015)
ID A B C AVG------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
Friends,
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column,
PLz help me out.......
Thank you.
There is no need for anything sophisticated here. In this case, brute force is the shortest and most likely the fastest. The "+1" thing in the code below is to meet your "round up" requirements without actually using a function. If the columns are of an integer datatype, then this is all integer math as you requested.
UPDATE #TestTable
SET [Avg] = CASE
WHEN A<B AND A<C THEN (B+C+1)/2
WHEN B<A AND B<C THEN (A+C+1)/2
ELSE (A+B+1)/2
END
;
If someone desires to check for performance, here's my normal million row test table.
--===== If the test table exists, drop it to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on the fly.
SELECT TOP 1000000
ID = IDENTITY(INT,1,1)
,A = ABS(CHECKSUM(NEWID()))%30
,B = ABS(CHECKSUM(NEWID()))%30
,C = ABS(CHECKSUM(NEWID()))%30
,[AVG] = CAST(0 AS INT)
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
And, of course, the following query demonstrates that the data provided by the OP is also calculated correctly according to the OP.
--===== Display the answers for given rows.
SELECT tt.*
FROM #TestTable tt
JOIN (
SELECT 08,09,10 UNION ALL
SELECT 10,25,26 UNION ALL
SELECT 09,15,16
)d(A,B,C)
ON tt.A = d.A
AND tt.B = d.B
AND tt.C = d.C
;
Of course, this doesn't work as expected if NULLs are present. You'd have to define how you'd like those to be treated especially if there's more than 1 per row.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2015 at 11:40 pm
Uday3421 (5/3/2015)
ID A B C AVG------------------------
1 08 09 10 10
------------------------
2 10 25 26 26
------------------------
3 09 15 16 16
------------------------
i trying to get the average of two largest number from the column a,b&c for the particular identity and store it into he average column...
Like,
for ID=1
the two largest numbers are 10 and 09 and the average of that two number is (10+9)/2=10.
(25+26)/2=26.
and
(15+16)/2=16.
the result should not come in the float number......
Didn't see this post before I posted the previous code, here is an adjustment to it according to these requirements.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TAVG TABLE
(
ID INT NOT NULL
,A INT NOT NULL
,B INT NOT NULL
,C INT NOT NULL
,[AVG] INT NULL
);
INSERT INTO @TAVG(ID,A,B,C)
VALUES
(1,08,09,10)
,(2,10,25,26)
,(3,09,15,16);
;WITH BASE_DATA AS
(
SELECT
A.ID
,ROW_NUMBER() OVER
(
PARTITION BY A.ID
ORDER BY X.VAL DESC
) AS X_DRID
,X.VAL
FROM @TAVG A
CROSS APPLY
(
SELECT A.A UNION ALL
SELECT A.B UNION ALL
SELECT A.C
) AS X(VAL)
)
,AVG_DATA AS
(
SELECT
BD.ID
,CEILING(SUM(BD.VAL) / 2.0) AS AVG_VAL
FROM BASE_DATA BD
WHERE BD.X_DRID < 3
GROUP BY BD.ID
)
UPDATE TA
SET TA.[AVG] = AD.AVG_VAL
FROM @TAVG TA
INNER JOIN AVG_DATA AD
ON TA.ID = AD.ID
;
SELECT
A.ID
,A.A
,A.B
,A.C
,A.[AVG]
FROM @TAVG A;
Results
ID A B C AVG
----------- ----------- ----------- ----------- -----------
1 8 9 10 10
2 10 25 26 26
3 9 15 16 16
May 4, 2015 at 12:05 am
Thank you so much to all........
May 4, 2015 at 12:20 am
Jeff Moden (5/3/2015)
There is no need for anything sophisticated here. In this case, brute force is the shortest and most likely the fastest. The "+1" thing in the code below is to meet your "round up" requirements without actually using a function. If the columns are of an integer datatype, then this is all integer math as you requested.
You are right Jeff, simpler is faster.
😎
May 4, 2015 at 7:19 am
Uday3421 (5/4/2015)
Thank you so much to all........
You're welcome. Do you understand how it all works?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2015 at 10:04 am
Yes i do Jeff Sir. Thank you once again....
May 4, 2015 at 10:39 am
Excellent. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply