SQL query missing expression?

[sports_brah]

SELECT CustomerNumber.invoices, COUNT(InvoiceNumber.invoices) AS NumberofInvoices,
FROM Invoices INNER JOIN customers
ON customernumber.invoices = customernumber.customers
GROUP BY CustomerNumber
HAVING CustomerNumber=1;


I'm trying to get the number of invoices sent to one person but its spitting up an error on line 2.

Anyone got a quick tip? Thanks.

 

[ruffneck]

Its tables before columns.

table.column not column.table

 

[planty]

Your table names need to be on the left-hand-side of the dot - so if your table name is Invoices you should be selecting 'Invoices.CustomerNumber', not 'CustomerNumber.Invoices'

 

[sports_brah]

Thanks, I corrected this, but it is still saying error on line 2, missing expression.

 

[planty]

Shouldn't have a comma before FROM

 

[sports_brah]

Also another query problem I have is:


SELECT purchaseordernumber, SUM(quantityordered*costpriceperunit)
FROM purchaseorderline
GROUP BY purchaseordernumber
HAVING purchaseordernumber=10005;

I'm trying to find the total cost of purchase order (as shown below)

Any ideas? Really appreciate it.

 

[sports_brah]

Shouldn't have a comma before FROM

Thanks, I got rid of it, and swapped a line down, and seem to have made some progress.

SELECT CustomerNumber.invoices, COUNT(InvoiceNumber.invoices) AS NumberofInvoices
FROM Invoices INNER JOIN customers
ON invoices.customernumber =customers.customernumber
GROUP BY CustomerNumber
HAVING CustomerNumber=1;


It is now saying line 5 is ambiguously defined (having customernumber=1).

Is there a better way to say it?

 

[planty]

You shouldn't need to use HAVING in that particular query - try it with 'WHERE CustomerNumber=1' instead.

Your other issue to do with multiplying and sum looks ok to me.. try sticking an alias on the SUM() column, so it's:
SELECT purchaseordernumber, SUM(quantityordered*costpriceperunit) AS Total

 

[sports_brah]

You shouldn't need to use HAVING in that particular query - try it with 'WHERE CustomerNumber=1' instead.

Your other issue to do with multiplying and sum looks ok to me.. try sticking an alias on the SUM() column, so it's:
SELECT purchaseordernumber, SUM(quantityordered*costpriceperunit) AS Total

changing it to where, eg

SELECT CustomerNumber.invoices, COUNT(InvoiceNumber.invoices) AS NumberofInvoices
FROM Invoices INNER JOIN customers
ON invoices.customernumber =customers.customernumber
GROUP BY CustomerNumber
WHERE CustomerNumber=1;

gives the error SQL command not properly ended.



The other issue when I do that just gives the result:

 

[elmarko]

Using my data so it makes sense.

Just change the field names, table names - basically your where needs to be before the group by.

SELECT

elec.ADDRESS,
COUNT(elec.MPAN + gas.mprn) AS STUFF

FROM dbo.elec_stuff elec INNER JOIN dbo.gas_stuff gas

ON gas.ADDRESS= elec.ADDRESS

WHERE elec.DATA_ITEM = 'STUFF'

GROUP BY elec.ADDRESS

 

[planty]

Sorry, the WHERE clause should be before the GROUP BY...

SELECT CustomerNumber.invoices, COUNT(InvoiceNumber.invoices) AS NumberofInvoices
FROM Invoices INNER JOIN customers
ON invoices.customernumber =customers.customernumber
WHERE CustomerNumber=1
GROUP BY CustomerNumber;

The answer to your other query is correct:
20*4 = 80
5*4 = 20
80+20 = 100

 

[sports_brah]

Sorry, the WHERE clause should be before the GROUP BY...

SELECT CustomerNumber.invoices, COUNT(InvoiceNumber.invoices) AS NumberofInvoices
FROM Invoices INNER JOIN customers
ON invoices.customernumber =customers.customernumber
WHERE CustomerNumber=1
GROUP BY CustomerNumber;

The answer to your other query is correct:
20*4 = 80
5*4 = 20
80+20 = 100

I just noticed the other one was correct, my bad!

SELECT CustomerNumber.invoices, COUNT(InvoiceNumber.invoices) AS NumberofInvoices
FROM Invoices INNER JOIN customers
ON invoices.customernumber =customers.customernumber
WHERE CustomerNumber=1
GROUP BY CustomerNumber;

It now says it is ambigously defined.

 

[Ascendancy]

It's ambiguous because there's a field called CustomerNumber in both tables surely?

 

[planty]

Yea, the table needs to be specified because of the join - should have spotted that!

 

[sports_brah]

It's ambiguous because there's a field called CustomerNumber in both tables surely?

So how would I work my way round it? Is it possible to do so?

 

[Ascendancy]

WHERE table.column = 1
GROUP BY table.column

 

[BigChief]

Try this,

SELECT

​

Inv.CustomerNumber

​

, COUNT(inv.InvoiceNumber) AS NumberofInvoices
FROM

​

Invoices Inv
JOIN

​

customers cus
ON

​

inv.customernumber = cus.customernumber
WHERE

​

cus.CustomerNumber=1
GROUP BY

​

cus.CustomerNumber

 

[sports_brah]

You guys are wonderful, thank you so much for your help.

 

[elmarko]

You need to specify the table & column names everywhere if they are shared.

Edit - ahh beaten like a red headed stepchild.