October 5, 2008 at 2:09 pm
I attempting to create a query that meets the following criteria:
LineItemCount is the number of entries in the InvoiceLineItems table that belong to a specific Account NUmber. I only want the LineItemCounts greater than 1.
LineItemSUM is the sum of the InvoiceLineItemAmount for a specific Account Number.
These are my tables:
Invoice_Line_Items Table
InvoiceID
InvoiceSequence
AccountNo
InvoiceLineItemAmount
InvoiceLineItemDescription
GLAccount Table
AccountNo
AccountDescription
This is what I've got so far:
Select AccountDescription, Count(*) AS LineItemCount, SUM(InvoiceLineItemAmount) AS Line_Item_Sum
From InvoiceLineItems JOIN GLAccounts
ON InvoiceLineItems.AccountNo=GLAccounts.AccountNo
Where LineItemCount > 1
Group By AccountDescription
I'm getting the following error:
Invalid column name 'LineItemCount'.
Any help appreciated.
October 5, 2008 at 2:22 pm
Try this. You have to use a having clause in order to check the count.
Select AccountDescription,
Count(1) AS LineItemCount,
SUM(InvoiceLineItemAmount) AS Line_Item_Sum
From InvoiceLineItems
JOIN GLAccounts
ON InvoiceLineItems.AccountNo=GLAccounts.AccountNo
Group By AccountDescription
HAVING Count(1) > 1
October 5, 2008 at 4:02 pm
Hope this answers your question, but is obvious that you have numerous mistakes in your statement. (Hopefully only typographical)
From InvoiceLineItems JOIN GLAccounts
your tables were given as:
Invoice_Line_Items
- note the underscores
GLAccount
- note missing "s"
'LineItemCount' is not the name of a column in a table
Correct the typographical errors and use Ken Simmons
HAVING Count(1) > 1
should work.
Select AccountDescription, Count(*) AS 'LineItemCount', SUM(InvoiceLineItemAmount) AS Line_Item_Sum
From Invoice_Line_Items JOIN GLAccount
ON Invoice_Line_Items.AccountNo=GLAccount.AccountNo
Group By AccountDescription
Having Count(1) > 1
October 5, 2008 at 7:46 pm
Works now....Thank you for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply