how to Diff vs Sum in Group By query?

Hello,
if area 'A' contains 2 numbers in 2 rows then
Select area, Sum(number) from tbl1 where area = 'A'
Group By area
gives me the sum of these 2 numbers in area 'A'
But how can I retrieve the difference of these 2 numbers
using T-Sql?
Thanks,
RonRon wrote:
> Hello,
> if area 'A' contains 2 numbers in 2 rows then
> Select area, Sum(number) from tbl1 where area = 'A'
> Group By area
> gives me the sum of these 2 numbers in area 'A'
> But how can I retrieve the difference of these 2 numbers
> using T-Sql?
> Thanks,
> Ron
Max(number) - Min(number)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks. That is pretty . I forgot to include a
twist in here. I also have a datefld. So num1 may be min
or num may be max. I have to show +num or -num.
select area, (First(num) - Last(num)) as num1 from tbl1
where datefld between '1/1/2005' and 1/2/2005' Group By
area having area = 'A'
I was able to use your trick to get my positive or
negative result using First and Last functions. Any
suggestions appreciated if this is incorrect usage.
Thanks again,
Ron

>--Original Message--
>Ron wrote:
>Max(number) - Min(number)
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Ron wrote:
> Thanks. That is pretty . I forgot to include a
> twist in here. I also have a datefld. So num1 may be min
> or num may be max. I have to show +num or -num.
> select area, (First(num) - Last(num)) as num1 from tbl1
> where datefld between '1/1/2005' and 1/2/2005' Group By
> area having area = 'A'
> I was able to use your trick to get my positive or
> negative result using First and Last functions. Any
> suggestions appreciated if this is incorrect usage.
First? Last? You must be using Access ... This is a SQL Server group
Do you need a SQL Server (Transact-SQL) solution? Those fnctions do not
exist in T-SQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:

>Thanks. That is pretty . I forgot to include a
>twist in here. I also have a datefld. So num1 may be min
>or num may be max. I have to show +num or -num.
>select area, (First(num) - Last(num)) as num1 from tbl1
>where datefld between '1/1/2005' and 1/2/2005' Group By
>area having area = 'A'
>I was able to use your trick to get my positive or
>negative result using First and Last functions. Any
>suggestions appreciated if this is incorrect usage.
Hi Ron,
Try if this helps:
SELECT G.Area, F.num - L.num AS num1
FROM (SELECT area, MIN(datefld) AS FDate, MAX(datefld) AS LDate
FROM tbl1
WHERE datefld BETWEEN '20050101' AND '20050201'
GROUP BY area) AS G
INNER JOIN tbl1 AS F
ON F.area = G.area
AND F.datefld = G.FDate
INNER JOIN tbl1 AS L
ON L.area = G.area
AND L.datefld = G.LDate
WHERE G.area = 'A'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SELECT A.area, A.num-B.num
FROM Tbl1 AS A
JOIN Tbl1 AS B
ON A.datefld < B.datefld
AND A.area = 'A'
AND B.area = 'A'
David Portas
SQL Server MVP
--|||Yes, I figured that out. I was writing the sql in Access
and transferring in to Query Analyzer. Sorry bout that.
I did end up creating a udf for first and last.

>--Original Message--
>Ron wrote:
min
>First? Last? You must be using Access ... This is a SQL
Server group
>Do you need a SQL Server (Transact-SQL) solution? Those
fnctions do not
>exist in T-SQL.
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Thanks. I will give that a try.

>--Original Message--
>On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:
>
min
>Hi Ron,
>Try if this helps:
>SELECT G.Area, F.num - L.num AS num1
>FROM (SELECT area, MIN(datefld) AS FDate, MAX
(datefld) AS LDate
> FROM tbl1
> WHERE datefld BETWEEN '20050101'
AND '20050201'
> GROUP BY area) AS G
>INNER JOIN tbl1 AS F
> ON F.area = G.area
> AND F.datefld = G.FDate
>INNER JOIN tbl1 AS L
> ON L.area = G.area
> AND L.datefld = G.LDate
>WHERE G.area = 'A'
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks very much for your reply. I will give this a try.

>--Original Message--
>SELECT A.area, A.num-B.num
> FROM Tbl1 AS A
> JOIN Tbl1 AS B
> ON A.datefld < B.datefld
> AND A.area = 'A'
> AND B.area = 'A'
>--
>David Portas
>SQL Server MVP
>--
>.
>