calculated valu

hello,
in a table with 2 fields (department and sales) i have to calculate the
sales for the company and the sales excluded spme departments.
example original table
OnlyDepA 100
OnlyDepB 200
OnlyDepC 500
.........
AllDep. 10000
What i need to calculate
What are the sales forAllDep without the sales for (OnlyDepA or ...)
example how the calculation is made,
AllDep. -OnlyDepA =10000 -100=9900
AllDep. -OnlyDepB =10000 -200=9800
........
I need to get the values
AllDep. 10000 EUR
AllDep. - OnlyDepA=9900 EUR
AllDep. - OnlyDepB=9800 EUR
......
thanks
XavierSee if this is what you need:
SELECT S1.dep, S1.amt, S2.amt - S1.amt
FROM sales AS S1, sales AS S2
WHERE S2.dep = 'AllDep' ;
I'm assuming that DEP is unique. If it isn't then please post some
accurate DDL so that we don't have to guess what the keys and
constraints are.
David Portas
SQL Server MVP
--|||hello David,
great, your example works perfect, if the value DEP is unique.
In my case i have also a field WNr.
For a W the values are unique, also the value (Dep + WNr) are unique
for all records in the table. How must i modify the statement in this case.
w
39 All 1000
39 DepA 100
39 DepB 200
...
40 All 3000
40 DepA 400
40 DepB 500
...
41 All 2000
41 DepA 300
41 DepB 200
..
I also have to calculte somthing like
AllDep. -(OnlyDepA + OnlyDepA)=1000-(100+200)=700 for each w
thanks
Xavier|||Here's a slightly different approach
SELECT wnr, dep_a, dep_b, dep_all,
(dep_all - dep_a) AS expr1,
(dep_all - dep_a -dep_b) AS expr2
FROM
(SELECT wnr,
SUM(CASE WHEN dep = 'DepA' THEN amt END) AS dep_a,
SUM(CASE WHEN dep = 'DepB' THEN amt END) AS dep_b,
SUM(CASE WHEN dep = 'AllDep' THEN amt END) AS dep_all
FROM sales
GROUP BY wnr) AS T ;
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
I hope that you know that a table cannot include a grand total, but
then you did not know that rows are not records; fields are not
columns; and tables are not files.
SELECT SUM(CASE WHEN dept_nbr = 'dept_1' THEN 0.00 ELSE foobar_amt END)
AS not_dept_1,
SUM(CASE WHEN dept_nbr = 'dept_2' THEN 0.00 ELSE foobar_amt END) AS
not_dept_2,
etc.
FROM Sales AS S1;|||David, thanks for your example, this is exactly for what i looked.
best regard,
Xavier
"David Portas" wrote:

> Here's a slightly different approach
> SELECT wnr, dep_a, dep_b, dep_all,
> (dep_all - dep_a) AS expr1,
> (dep_all - dep_a -dep_b) AS expr2
> FROM
> (SELECT wnr,
> SUM(CASE WHEN dep = 'DepA' THEN amt END) AS dep_a,
> SUM(CASE WHEN dep = 'DepB' THEN amt END) AS dep_b,
> SUM(CASE WHEN dep = 'AllDep' THEN amt END) AS dep_all
> FROM sales
> GROUP BY wnr) AS T ;
> --
> David Portas
> SQL Server MVP
> --
>