compute diffrence

Hi all
I have a table like this
Code Price Type
1 100 1
2 300 0
1 1500 0
3 50 0
4 100 1
1 50 1
I want to compute the Sum of diffrence price between those rows that have
diffrent Type group by their Code..
How can I do that'
thxselect
(
select sum(price) from tablex group by type
where type=1
) -
(
select sum(price) from tablex group by type
where type=2
)
as difference
"perspolis" wrote:

> Hi all
> I have a table like this
> Code Price Type
> 1 100 1
> 2 300 0
> 1 1500 0
> 3 50 0
> 4 100 1
> 1 50 1
> I want to compute the Sum of diffrence price between those rows that have
> diffrent Type group by their Code..
> How can I do that'
> thx
>
>|||Jose,
Your SQL statement will not compile because you use where after group
by.

> select
> (
> select sum(price) from tablex group by type
> where type=1
> ) -
> (
> select sum(price) from tablex group by type
> where type=2
> )
> as difference
Also, I think the original poster wants the sum of difference between
those rows that have different Type group by their code. Thank you.
"jose g. de jesus jr mcp, mcdba" wrote:
> select
> (
> select sum(price) from tablex group by type
> where type=1
> ) -
> (
> select sum(price) from tablex group by type
> where type=2
> )
> as difference
>
>
> "perspolis" wrote:
>|||sorry no sql here. the idea is there though use subquery
select
(
select sum(price) from tablex where type=1
group by type
) -
(
select sum(price) from tablex where type=2
group by type
)
as difference
"frank chang" wrote:
> Jose,
> Your SQL statement will not compile because you use where after grou
p
> by.
>
> Also, I think the original poster wants the sum of difference between
> those rows that have different Type group by their code. Thank you.
>
> "jose g. de jesus jr mcp, mcdba" wrote:
>|||no that's not right..
I want to group by Code not type
<jose g. de jesus jr mcp>; "mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:87147408-0BA3-4E4E-8A1F-A6E811A0882D@.microsoft.com...
> sorry no sql here. the idea is there though use subquery
> select
> (
> select sum(price) from tablex where type=1
> group by type
> ) -
> (
> select sum(price) from tablex where type=2
> group by type
> )
> as difference
>
>
> "frank chang" wrote:
>
group
have|||On Tue, 23 Aug 2005 15:35:04 +0430, perspolis wrote:

>Hi all
>I have a table like this
>Code Price Type
> 1 100 1
> 2 300 0
> 1 1500 0
> 3 50 0
> 4 100 1
> 1 50 1
>I want to compute the Sum of diffrence price between those rows that have
>diffrent Type group by their Code..
>How can I do that'
>thx
>
Hi perspolis,
Thanks for posting sample data. Unfortunately, I'm not sure what your
requirements are. Maybe you can improve my understanding by posting the
output you'd expect from the sample data above?
Also, posting the table structure as CREATE TABLE statement and the
sample data as INSERT statements would make it easier for me to test my
solutions.
Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The output is like this
Code Total
1 1350
2 300
3 50
4 -100
thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:be5ng1htpq5m0gcgubq8ortulkvs6dcmq0@.
4ax.com...
> On Tue, 23 Aug 2005 15:35:04 +0430, perspolis wrote:
>
> Hi perspolis,
> Thanks for posting sample data. Unfortunately, I'm not sure what your
> requirements are. Maybe you can improve my understanding by posting the
> output you'd expect from the sample data above?
> Also, posting the table structure as CREATE TABLE statement and the
> sample data as INSERT statements would make it easier for me to test my
> solutions.
> Check out www.aspfaq.com/5006.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||can you pls enlightened me more
how did code 1 got 1350 and
how did 4 get -100
"perspolis" wrote:

> The output is like this
> Code Total
> 1 1350
> 2 300
> 3 50
> 4 -100
> thanks
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:be5ng1htpq5m0gcgubq8ortulkvs6dcmq0@.
4ax.com...
>
>|||to get this output for example for Code 1
Add Sum(Price) of those rows with Type=0 and Code=1 minus Sum( Price) of
those rows with Type=1 and Code=1 to get 1500-(100+50)= 1350 for
Code=1
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:4CF3C365-1AA9-4417-AF1F-2346915FE922@.microsoft.com...
> can you pls enlightened me more
> how did code 1 got 1350 and
> how did 4 get -100
>
> "perspolis" wrote:
>|||persoplis, This is a first cut but it shows the desired results
select a1.code, a1.sumprice - a2.sumprice
FROM (select t1.code, sum(t1.price) as sumprice from xyz t1
group by t1.code, t1.type ) AS a1
JOIN
(select t1.code, sum(t1.price) as sumprice from xyz t1
group by t1.code, t1.type ) AS a2
ON a1.code = a2.code and a1.sumprice > a2.sumprice
UNION
select a1.code, a1.sumprice
from (select t1.code, sum(t1.price) as sumprice from xyz t1
group by t1.code, t1.type ) AS a1
where (select count(*) from
(select t1.code, sum(t1.price) as sumprice from xyz t1
group by t1.code, t1.type ) AS a2
where a1.code = a2.code) = 1
This is the DDL I used:
Create Table XYZ
(
Code int,
Price int,
Type int
)
go
INSERT into XYZ values (1,100,1)
INSERT into XYZ values (2,300,0)
INSERT into XYZ values (1,1500,0)
INSERT into XYZ values (3,50,0)
INSERT into XYZ values (4,100,1)
INSERT into XYZ values (1,50,1)
INSERT into XYZ values (5,200,1)
INSERT into XYZ values (5,2500,0)
INSERT into XYZ values (5,250,1)
go
"perspolis" wrote:

> The output is like this
> Code Total
> 1 1350
> 2 300
> 3 50
> 4 -100
> thanks
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:be5ng1htpq5m0gcgubq8ortulkvs6dcmq0@.
4ax.com...
>
>