Are you interested in becoming an AWS Certified Solutions Architect?
Discover a new interactive way of training for the exam.
Course Of The Month
7 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sql sum statement
Posted on 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.intsalespric
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.intsalespric
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2
7 Comments
Active today
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.intsalespric
FROM Customers INNER JOIN Invoice ON Customers.intCustomerID = Invoice.intCustomerID
ORDER BY Invoice.datInvoiceDate;
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.intsalespric
FROM Customers INNER JOIN Invoice ON Customers.intCustomerID = Invoice.intCustomerID
ORDER BY Invoice.datInvoiceDate;
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.intquanti
FROM (Customers INNER JOIN Invoice ON Customers.intCustomerID = Invoice.intCustomerID) INNER JOIN InvoiceLines ON Invoice.intInvoiceNumber = InvoiceLines.intInvoiceNum
Group BY Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName AS Cname, Customers.txtFirstName+' '+Customers.txtLastName AS Name
Order BY Invoice.datInvoiceDate;
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.intquanti
FROM (Customers INNER JOIN Invoice ON Customers.intCustomerID = Invoice.intCustomerID) INNER JOIN InvoiceLines ON Invoice.intInvoiceNumber = InvoiceLines.intInvoiceNum
Group BY Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName AS Cname, Customers.txtFirstName+' '+Customers.txtLastName AS Name
Order BY Invoice.datInvoiceDate;
Active 7 days ago
Try changing your group by clause to:
Group BY Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName, Customers.txtFirstName, Customers.txtLastName
Group BY Invoice.datInvoiceDate, Invoice.intInvoiceNumber, Customers.txtCompanyName, Customers.txtFirstName, Customers.txtLastName
Featured Post
Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Suggested Courses
Course of the Month7 days, 4 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
724 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.