help with query

Hi to all,
I would like some help with a query, taking in count this two tables.
Table people:
ID Name
-- --
1 P1
2 P2
3 P3
Table Contract
Number Quantity PeopleID
-- -- --
000000 500 1
000000 300 2
111111 1000 1
545454 100 3
The expected result is in the case of the people id = 1:
500+1000=1500
+
The quantities that have the same contract number. in this case 300
Total result for peopleid=1 ->1800
The expected result is in the case of the people id = 2:
300+the quantities that have the same contract number. In this case 500
Total result for peopleid=2 ->800
Thanks
Regards.
JosemaHi Josema,
Perhaps the following query might help you:
DECLARE @.ID INT
SET @.ID = 1
SELECT SUM(QUANTITY) FROM CONTRACT WHERE NUMBER IN
(SELECT DISTINCT NUMBER FROM CONTRACT WHERE PEOPLEID = @.ID)
Haven't tested above code thorough, but it seemed to work, as described by
you.
Good luck!
Regards,
Van
"Josema" wrote:

> Hi to all,
> I would like some help with a query, taking in count this two tables.
> Table people:
> ID Name
> -- --
> 1 P1
> 2 P2
> 3 P3
> Table Contract
> Number Quantity PeopleID
> -- -- --
> 000000 500 1
> 000000 300 2
> 111111 1000 1
> 545454 100 3
> The expected result is in the case of the people id = 1:
> 500+1000=1500
> +
> The quantities that have the same contract number. in this case 300
> Total result for peopleid=1 ->1800
> The expected result is in the case of the people id = 2:
> 300+the quantities that have the same contract number. In this case 500
> Total result for peopleid=2 ->800
> --
> Thanks
> Regards.
> Josema|||Here are some ideas that should point you in the correct direction.
CREATE TABLE #People
( ID int
, [Name] varchar(25)
)
CREATE Table #Contract
( [Number] varchar(20)
, Quantity int
, PeopleID int
)
SET NOCOUNT ON
INSERT INTO #People VALUES ( 1, 'P1' )
INSERT INTO #People VALUES ( 2, 'P2' )
INSERT INTO #People VALUES ( 3, 'P3' )
INSERT INTO #Contract VALUES ( '000000', 500, 1 )
INSERT INTO #Contract VALUES ( '000000', 300, 2 )
INSERT INTO #Contract VALUES ( '111111', 1000, 1 )
INSERT INTO #Contract VALUES ( '545454', 100, 3 )
-- The expected result is in the case of the people id = 1:
-- 500+1000=1500
SELECT
c.PeopleID
, sum (Quantity )
FROM #People p
JOIN #Contract c
ON p.ID = c.PeopleID
WHERE c.PeopleID = 1
GROUP BY c.PeopleID
-- The quantities that have the same contract number. in this case 300
-- Total result for peopleid=1 ->1800
SELECT
Totals = sum( Quantity )
FROM #People p
JOIN #Contract c
ON p.ID = c.PeopleID
WHERE ( c.PeopleID = 1
OR c.[Number] IN ( SELECT [Number]
FROM #Contract
WHERE PeopleID = 1
)
)
-- The expected result is in the case of the people id = 2:
-- 300+the quantities that have the same contract number. In this case 500
-- Total result for peopleid=2 ->800
SELECT
Totals = sum( Quantity )
FROM #People p
JOIN #Contract c
ON p.ID = c.PeopleID
WHERE ( c.PeopleID = 2
OR c.[Number] IN ( SELECT [Number]
FROM #Contract
WHERE PeopleID = 2
)
)
DROP TABLE #People
DROP TABLE #Contract
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Josema" <Jestrade@.ocu.org> wrote in message news:D6703F12-39E9-422A-940C-CE443F9347AD@.micro
soft.com...
> Hi to all,
>
> I would like some help with a query, taking in count this two tables.
>
> Table people:
>
> ID Name
> -- --
> 1 P1
> 2 P2
> 3 P3
>
> Table Contract
>
> Number Quantity PeopleID
> -- -- --
> 000000 500 1
> 000000 300 2
> 111111 1000 1
> 545454 100 3
>
> The expected result is in the case of the people id = 1:
>
> 500+1000=1500
> +
> The quantities that have the same contract number. in this case 300
>
> Total result for peopleid=1 ->1800
>
> The expected result is in the case of the people id = 2:
>
> 300+the quantities that have the same contract number. In this case 500
>
> Total result for peopleid=2 ->800
>
> --
> Thanks
> Regards.
> Josema