SQL problems

[spitfire-ian]

Can someone tell me why I keep getting a syntax error when I try to run this query?

SELECT kpm.kpm_code AS Code, SUM(kpm.kpm_standard*(1000/kpm.kpm_weight)) AS Cost
FROM kpm kpm
WHERE (kpm.kpm_locn<>'FAIL') AND (kpm.kpm_stop<>'S')

I've only been fiddling with SQL since yesterday afternoon and I'm in a bit of a muddle!

[zaktoo]

spitfire-ian said:

Can someone tell me why I keep getting a syntax error when I try to run this query?

SELECT kpm.kpm_code AS Code, SUM(kpm.kpm_standard*(1000/kpm.kpm_weight)) AS Cost
FROM kpm kpm
WHERE (kpm.kpm_locn<>'FAIL') AND (kpm.kpm_stop<>'S')

I've only been fiddling with SQL since yesterday afternoon and I'm in a bit of a muddle!

You don't say what flavour of SQL, but smells a bit like MSSQL to me...

First, what is the actual unabridged error?

Secondly, simplify - rewrite as
SELECT kpm_code AS Code, SUM(kpm_standard*(1000/kpm_weight)) AS Cost
FROM kpm
WHERE (kpm_locn<>'FAIL') AND (kpm_stop<>'S')

although I suspect the "SUM" is causing grief.

Ciao

Zak

[grumbledoak]

That SUM() is ugly, may well be the cause.
This is simpler, and should be the same if my maths is still with me:

SELECT kpm_code AS Code, 1000 * SUM(kpm_standard / kpm_weight) AS Cost
FROM kpm
WHERE (kpm_locn<>'FAIL') AND (kpm_stop<>'S')

What error do you get ?

[jamieboy]

Fairly sure you need to add a 'GROUP BY kpm_code' at the end.

[spitfire-ian]

zaktoo said:

You don't say what flavour of SQL, but smells a bit like MSSQL to me...

The code is produced by MS Query so I guess it must be.

zaktoo said:

First, what is the actual unabridged error?

All I get is syntax error

[zaktoo]

spitfire-ian said:

zaktoo said:

You don't say what flavour of SQL, but smells a bit like MSSQL to me...

The code is produced by MS Query so I guess it must be.

zaktoo said:

First, what is the actual unabridged error?

All I get is syntax error

As Jamieboy says you're probably missing the group by clause or sum is not doing what you expect.
I'd also enclose the division bit with a test for the divisor being 0 and returning null if it is, continuing with the formula if no, once you've got a hold of the actual syntactical error.


HTH

Ciao

Zak

[spitfire-ian]

I took what Grumbledoak and Jamieboy said and have come up with

SELECT kpm_code AS Code, 1000 * SUM(kpm_standard / kpm_weight) AS Cost
FROM kpm
WHERE (kpm_locn<>'FAIL') AND (kpm_stop<>'S')
GROUP BY kpm-code

Is that right?

It now gives me an error saying cannot find kpm in any local table

[zaktoo]

spitfire-ian said:

I took what Grumbledoak and Jamieboy said and have come up with

SELECT kpm_code AS Code, 1000 * SUM(kpm_standard / kpm_weight) AS Cost
FROM kpm
WHERE (kpm_locn<>'FAIL') AND (kpm_stop<>'S')
GROUP BY kpm-code

Is that right?

It now gives me an error saying cannot find kpm in any local table

that's GROUP BY kpm_code

[jamieboy]

spitfire-ian said:

I took what Grumbledoak and Jamieboy said and have come up with

SELECT kpm_code AS Code, 1000 * SUM(kpm_standard / kpm_weight) AS Cost
FROM kpm
WHERE (kpm_locn<>'FAIL') AND (kpm_stop<>'S')
GROUP BY kpm-code

Is that right?

It now gives me an error saying cannot find kpm in any local table

the text you've put here has 'GROUP BY kpm-code' not 'GROUP BY kpm_code' - not sure if that's a typo here or in your query.

[spitfire-ian]

Sorry, that was a typo by me.

Back to our old friend the syntax error again.

[jamieboy]

I've just knocked up a quick table to test, and this is a cut-and-paste of a query that works for me:

SELECT kpm_code AS Code, SUM(kpm_standard*(1000/kpm_weight)) AS Cost
FROM kpm
WHERE (kpm_locn<>'FAIL') AND (kpm_stop<>'S') GROUP BY kpm_code

[FunkyGibbon]

spitfire-ian said:

Sorry, that was a typo by me.

Back to our old friend the syntax error again.

you doing this in MSQuery from Excel?

If so I suspect you problem might be that MSQuery doesn't like the construct

select a As b

it will replace b with blank

so if you have

select a AS b, c AS d

it will have 2 blanks which may throw the error.

try removing one of the AS bits.

[spitfire-ian]

Using Jamieboy's code and FunkyGibbon's advice about the 'AS' it now works apart from the column aliases. I was using MS Query in Excel so that seems to be the root of my problems.

[Jinx]

Why the "As" clause anyway - doesn't it work if you just have a space instead?

[spitfire-ian]

Jinx said:

Why the "As" clause anyway - doesn't it work if you just have a space instead?

In the end I did realise I didn't need them, I was just trying to change the column headings but in the end that wasn't needed.