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.