July 25, 2016 at 7:55 am
--input code :
CREATE TABLE SumPositiveNegative
(
ID INT
)
GO
INSERT INTO SumPositiveNegative VALUES
(1),
(-1),
(2),
(-1),
(3),
(-3)
GO
select * from SumPositiveNegative
-- Expected_output :
SumIgnoring-ve_symbolsTotalSum+VeSum-VeSum
11 165
condition :
Friends i used multiple select statements in my query , please some one suggust me how to bring answer in Single Select Query
-- my Solution :
select SUM(abs(id)) as [SumIgnoring-ve_symbols] ,
(select sum(id) from SumPositiveNegative ) as count_id ,
(select sum(id) from SumPositiveNegative where ID like '[0-9]') as [+ve_sum],
abs((select sum(id) from SumPositiveNegative where ID like '-[0-9]')) as [-ve_sum]
from SumPositiveNegative
July 25, 2016 at 8:10 am
Anandkumar-SQL_Developer (7/25/2016)
--input code :CREATE TABLE SumPositiveNegative
(
ID INT
)
GO
INSERT INTO SumPositiveNegative VALUES
(1),
(-1),
(2),
(-1),
(3),
(-3)
GO
select * from SumPositiveNegative
-- Expected_output :
SumIgnoring-ve_symbolsTotalSum+VeSum-VeSum
11 165
condition :
Friends i used multiple select statements in my query , please some one suggust me how to bring answer in Single Select Query
-- my Solution :
select SUM(abs(id)) as [SumIgnoring-ve_symbols] ,
(select sum(id) from SumPositiveNegative ) as count_id ,
(select sum(id) from SumPositiveNegative where ID like '[0-9]') as [+ve_sum],
abs((select sum(id) from SumPositiveNegative where ID like '-[0-9]')) as [-ve_sum]
from SumPositiveNegative
Don't use additional queries when you're already using the same information. Don't use LIKE for numeric data. Use a CASE statement to discard information that you don't want to aggregate.
SELECT SUM(ABS(ID)) AS [SumIgnoring-ve_symbols],
SUM(ID) AS TotalSum,
SUM(CASE WHEN ID > 0 THEN ID ELSE 0 END) AS [+VeSum],
SUM(CASE WHEN ID < 0 THEN ABS(ID) ELSE 0 END) AS [-VeSum]
FROM SumPositiveNegative;
July 25, 2016 at 9:12 am
Odd problem. Was this a test or interview question?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 25, 2016 at 9:20 am
Hi Luis Cazares ,
Thank u very muh .
i didn't think using case. now i got one more good ides because of u.
July 31, 2016 at 12:19 pm
This actually hAS a lot of problems. A table hAS to have a key, but what you posted hAS no key, and can never have a key. Essentially what you have done is post a deck of punch cards written in SQL!
CREATE TABLE PositiveNegative
(i INTEGER NOT NULL
CHECK (i <> 0));
INSERT INTO PositiveNegative VALUES
(1), (-1), (2), (-1), (3), (-3);
>> I used multiple select statements in my query, please some one suggest me how to bring answer in Single Select Query <<
Using scalar subqueries in a select list is incredibly expensive. Treating an integer AS it wAS a string does not work in SQL; that is how we did it in COBOL 50 years ago..
SELECT SUM(ABS(i)) AS abs_i_tot,
COUNt(*) AS i_cnt,
SUM(i) AS i_tot,
SUM (CASE WHEN SIGN(i) = 1 THEN i ELSE 0 END) AS pos_i_tot,
SUM (CASE WHEN SIGN(i) = -1 THEN i ELSE 0 END) AS neg_i_tot,
FROM PositiveNegative;
As an exercise, try replacing the case expressions I used for clarity with function calls to ABS(), SIGN(), etc instead. This used to be important for we were working with low-level languages like C or assembly, but in fairness today good optimizers will do it for you.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 2, 2016 at 12:42 am
Hi CELKO,
Its working. thank u for your valuable time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply