Need Big Help : Sum And Substract

Sep 19, 2007

Hi,
to keep things short, I have a query like this : SELECT A AS A, SUM(B) AS B FROM CC WHERE A IN (1,2,3) GROUP BY A

I want to be able to SUM the field B when A is equal to 1, and substract the field B when A is equal to 2 or 3. So I know SUM is incorrect there, cuz sometimes the field B must be added, and sometimes it must be substracted. What's the easiest way to do it?

For example, if the fields B in the database has the values 3, 5, and 7, the query would go something like 3+5-7, giving 1 for B at the end.

Tanx for your help guyz!

View 3 Replies

Substract New Row From Old Row In Same Column?

Mar 12, 2008

Hi

I'm having a bit troubble creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:

Pricecalcid Date Price Itemid

2000 2006-12-12 3000 100

2488 2007-10-11 2800 100

3100 2008-08-07 2500 100

What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.

How do I do that?

Thanks

View 5 Replies View Related

Substract Weekends

Oct 22, 2007

I am getting two dates each of which I calculate using a subquery (joining 2 or 3 tables)
Then I perform datediff on these two dates to get a interger values (No of Days)

I need to substract the weekends from this No of days
How do I accomplish this

Thanks
sowree

View 10 Replies View Related

Substract The Sum Of Two Doc Types In A Table

Jun 4, 2006

I need to subtract the values of the totals of two document types in a table. So far I have the following with the results as shown.

SELECT TOP 100 PERCENT { fn MONTHNAME(DOCDATE) } AS [MONTH], DOCID, SUM(DOCAMNT) AS TOTAL
FROM dbo.tblSales
WHERE (DOCID = 'RTD') OR
(DOCID = 'INV')
GROUP BY DOCID, { fn MONTHNAME(DOCDATE) }
ORDER BY DOCID DESC

This results in the following:
Month DOCID TOTAL
MAY RTD 165752.87
MAY INV 18149416.37

I need one value showing the difference of the totals.
Thanks.

View 6 Replies View Related

Gradually Add O Substract Numbers In A Column

Nov 22, 2007

how do I gradually add o substract numbers in a column for example if I have

total
deposito
test

120

120

80
40

77

117

7

124

4
120

i need in the test column:
if the number is in the total column the number in test is added
if the number is in the deposito column the number in test is substracted

i have this query:

SELECT gir_cantidad as total,null as deposito,'operation' as test

FROM giros inner join corresponsales on cor_corresponsal_id = gir_corresponsal_id where gir_fecha >= '11/21/2007' and gir_fecha <= '11/22/2007 23:59:59' and gir_fecha_anul is null and gir_agencia_id = 1

UNION

select null as total,paa_valor as deposito,'operation' as test from pagosagencia where paa_fecha >= '11/21/2007' and paa_fecha <= '11/22/2007 23:59:59' and paa_agencia_id= 1 order by gir_fecha

In column test i need the functionality to add and subtract the columns total and deposito

Please help

View 4 Replies View Related

How To Substract New Rows From Old Rows In The Same Column?

Mar 7, 2008

Hi I'm having a bit troubble by creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:

Pricecalcid Date Price Itemid
2000 2006-12-12 3000 100
2488 2007-10-11 2800 100
3100 2008-08-07 2500 100

What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.

How do I do that?

Thanks

Morten

View 9 Replies View Related