Hi All,
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA0111.000022.0000
DVD0036.000053.5600
ZZZ0234.000044.5000
BMM0023.000029.8500
BMM0013.000032.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01blah blah 57750.0000115500.0000
BMM001blah blah blah 8739.000095692.0500
DVD003blah blah blabber DVD4230.000037759.8000
BMM002Yadda Yadda2772.000027581.4000
DVD001Dooddly doo DVD1605.000013658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.
Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
Showing posts with label quantity. Show all posts
Showing posts with label quantity. Show all posts
Friday, March 23, 2012
Help with select query
Hi All,
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA01 11.0000 22.0000
DVD003 6.0000 53.5600
ZZZ023 4.0000 44.5000
BMM002 3.0000 29.8500
BMM001 3.0000 32.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01 blah blah 57750.0000 115500.0000
BMM001 blah blah blah 8739.0000 95692.0500
DVD003 blah blah blabber DVD 4230.0000 37759.8000
BMM002 Yadda Yadda 2772.0000 27581.4000
DVD001 Dooddly doo DVD 1605.0000 13658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA01 11.0000 22.0000
DVD003 6.0000 53.5600
ZZZ023 4.0000 44.5000
BMM002 3.0000 29.8500
BMM001 3.0000 32.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01 blah blah 57750.0000 115500.0000
BMM001 blah blah blah 8739.0000 95692.0500
DVD003 blah blah blabber DVD 4230.0000 37759.8000
BMM002 Yadda Yadda 2772.0000 27581.4000
DVD001 Dooddly doo DVD 1605.0000 13658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
Help with select query
Hi All,
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA01 11.0000 22.0000
DVD003 6.0000 53.5600
ZZZ023 4.0000 44.5000
BMM002 3.0000 29.8500
BMM001 3.0000 32.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01 blah blah 57750.0000 115500.0000
BMM001 blah blah blah 8739.0000 95692.0500
DVD003 blah blah blabber DVD 4230.0000 37759.8000
BMM002 Yadda Yadda 2772.0000 27581.4000
DVD001 Dooddly doo DVD 1605.0000 13658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA01 11.0000 22.0000
DVD003 6.0000 53.5600
ZZZ023 4.0000 44.5000
BMM002 3.0000 29.8500
BMM001 3.0000 32.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01 blah blah 57750.0000 115500.0000
BMM001 blah blah blah 8739.0000 95692.0500
DVD003 blah blah blabber DVD 4230.0000 37759.8000
BMM002 Yadda Yadda 2772.0000 27581.4000
DVD001 Dooddly doo DVD 1605.0000 13658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
Friday, March 9, 2012
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
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
Subscribe to:
Posts (Atom)