Solved

Sql sum statement

Posted on 2013-05-15
7
429 Views
Last Modified: 2013-05-15
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
0
Comment
Question by:Moed
  • 3
  • 2
  • 2
7 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39167498
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
 
LVL 2

Author Comment

by:Moed
ID: 39167512
That brings up a dialog asking for the parameter invoicelines.quantity
0
 
LVL 2

Author Comment

by:Moed
ID: 39167603
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 6

Accepted Solution

by:
BurundiLapp earned 500 total points
ID: 39167659
Try changing your group by clause to:

Group BY  Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName, Customers.txtFirstName, Customers.txtLastName
0
 
LVL 2

Author Closing Comment

by:Moed
ID: 39167689
This was not really an answer to the original question but it did resolve the issue.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39167694
how do you run that query? in MS SQL?
0
 
LVL 6

Expert Comment

by:BurundiLapp
ID: 39167851
I think I should have an assist there and the answer awarded to emoreau.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now