Substract 2 values
-
Hello, I would like to minus from rows. 'Other Cost' minus the rest of the rows.
select 1 Period,'Main' Category,100 Value
union all
select 1 Period,'Cost' Category,5 Value
union all
select 1 Period,'OtherCost' Category,20000 ValueI tried like this but is not working. Can someone guide me.
select Period,Category
, SUM(CASE WHEN Category='Main' THEN [Value] ELSE 0 END) MOH
, SUM(CASE WHEN Category ='Cost' THEN [Value] ELSE 0 END) [Common Cost]
, SUM(CASE WHEN Category='OtherCost' THEN [Value] ELSE 0 END) [MOH Others]
from(
select 1 Period,'Main' Category,100 Value
union all
select 1 Period,'Cost' Category,5 Value
union all
select 1 Period,'OtherCost' Category,20000 Value
)x group by Period,CategoryExpected result
-
To subtract the values the rows would need to be joined in some way. Since maybe some Category(ies) is/are not always present for each Period you could try OUTER APPLY only when the Category is 'OtherCost'. There are lots of other ways to accomplish this tho. Maybe something like this
with testCostTable([Period], [Category], [Value]) as (
select 1, 'Main', 100
union all
select 1, 'Cost', 5
union all
select 1,'OtherCost', 20000)
select tct.[Period], tct.[Category],
tct.[Value]-isnull(oa.oc, 0) [Value]
from testCostTable tct
outer apply (select sum(oth.[Value]) oc
from testCostTable oth
where oth.[Period]=tct.[Period]
and oth.[Category]<>'OtherCost'
and tct.[Category]='OtherCost') oa;PeriodCategoryValue
1Main100
1Cost5
1OtherCost19895Also, the column names used are all SQL Server reserved words. That's something many SQL developers find annoying :). In my case it triggers OCD because intellisense assigns the word the color for the keyword and not as a label (which is what it should be). Therefore brackets, brackets, and more brackets
Also, when you say "minus from rows" from a set-wise perspective it could mean EXCEPT (which in Oracle is called MINUS). From the Docs: "EXCEPT returns distinct rows from the left input query that aren't output by the right input query." In this case it seems like this is not what you're looking for. Language-wise it would've been clearer to me if there was a distinction made between subtraction and "minus from rows"
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Steve Collins wrote:
with testCostTable([Period], [Category], [Value]) as (
Also, the column names used are all SQL Server reserved words. That's something many SQL developers find annoying :). In my case it triggers OCD because intellisense assigns the word the color for the keyword and not as a label (which is what it should be). Therefore brackets, brackets, and more brackets
Actually none of those are SQL reserved words. As such, brackets are not needed. [VALUE is an ODBC keyword but not reserved by SQL Server.]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Ok, Category appears to not be a reserved word and appears in white. Period and Value are both blue. So it's brackets, no brackets, and brackets instead of brackets, brackets, and more brackets. Thanks Scott
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Steve Collins wrote:
Ok, Category appears to not be a reserved word and appears in white. Period and Value are both blue. So it's brackets, no brackets, and brackets instead of brackets, brackets, and more brackets. Thanks Scott
SSMS showing a word in blue does not mean it's a reserved word in SQL Server.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Have you considered using a relational design instead of this mess? There is no such thing as a generic category in RDBMS; we follow the first law of logic that "to be is to be something in particular and to be everything in general or nothing in particular, is to be nothing at all". It is called the law of identity in a choosy part of the first course in formal logic you taken college.
You also failed to post any DDL. You also don't know how to create a table so you used the old Sybase row by row construction with union all. Is this what you might have meant?
CREATE TABLE Spreadsheet
(spreadsheet_nbr INTEGER NOT NULL PRIMARY KEY,
main_something DECIMAL (10, 2) DEFAULT 0.00 NOT NULL,
generic_cost DECIMAL (10, 2) DEFAULT 0.00 NOT NULL,
other_cost DECIMAL (10, 2) DEFAULT 0.00 NOT NULL);
A table must have a key. By definition, it is not an option. This is why for over 30 years, people posting to SQL forms have been told they need to post DDL. If the main cost, cost and other costs are actually attributes of the same entity, then they get their own column. What you've done is a design flaw called "attributes splitting"; this means thing that should have been part of the same entity have been spread out over different rows or even different tables. This is why you find that you have to repair your mistake by trying to do a group by. You also need better column names. Why do you think things like "main" or "cost" are exact and precise, easily understood by someone reading your code?
SELECT spreadsheet_nbr, main_something; generic_cost, other_cost, (generic_cost - other_cost) AS foobar
FROM Spreadsheet ;
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply