Sql sum statement

I have the following sql statement

SELECT Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName as Cname, Customers.txtFirstName + ' '+ Customers.txtLastName as Name,
IIF (Cname Is Null or Cname = '',Name, IIF (Name Is Null or Name = '',Cname,Cname+', '+ Name)) as FullName
FROM Customers INNER JOIN Invoice ON Customers.intCustomerID = Invoice.intCustomerID
ORDER BY Invoice.datInvoiceDate;

I have another table named InvoiceLines.  I want to add to the above sql statement the sum of invoicelines.quantity *invoicelines.intsalesprice where invoicelines.intInvoiceNumber=Invoice.intInvoiceNumber
LVL 2
MoedAsked:
Who is Participating?
Advertise Here
 
BurundiLappConnect With a Mentor Commented:
Try changing your group by clause to:

Group BY  Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName, Customers.txtFirstName, Customers.txtLastName
0
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried:

SELECT Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName as Cname, Customers.txtFirstName + ' '+ Customers.txtLastName as Name,
IIF (Cname Is Null or Cname = '',Name, IIF (Name Is Null or Name = '',Cname,Cname+', '+ Name)) as FullName
, (select invoicelines.quantity *invoicelines.intsalesprice from invoicelines where invoicelines.intInvoiceNumber=Invoice.intInvoiceNumber) as invoicetotal
FROM Customers INNER JOIN Invoice ON Customers.intCustomerID = Invoice.intCustomerID
ORDER BY Invoice.datInvoiceDate;
0
 
MoedAuthor Commented:
That brings up a dialog asking for the parameter invoicelines.quantity
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Access It Now
 
MoedAuthor Commented:
I've tried this but get a missing operator error in the group by clause

SELECT Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName AS Cname, Customers.txtFirstName+' '+Customers.txtLastName AS Name, IIf(Cname Is Null Or Cname='',Name,IIf(Name Is Null Or Name='',Cname,Cname+', '+Name)) AS FullName, Sum(InvoiceLines.intquantity*InvoiceLines.intSalesPrice) AS InvoiceTotal
FROM (Customers INNER JOIN Invoice ON Customers.intCustomerID = Invoice.intCustomerID) INNER JOIN InvoiceLines ON Invoice.intInvoiceNumber = InvoiceLines.intInvoiceNumber
Group BY  Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName AS Cname, Customers.txtFirstName+' '+Customers.txtLastName AS Name
Order BY Invoice.datInvoiceDate;
0
 
MoedAuthor Commented:
This was not really an answer to the original question but it did resolve the issue.
0
 
Éric MoreauSenior .Net ConsultantCommented:
how do you run that query? in MS SQL?
0
 
BurundiLappCommented:
I think I should have an assist there and the answer awarded to emoreau.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.