May 22, 2015 at 3:54 am
Hi,
I have got a query that I am working on but I can't get it working properly.
I am trying to group account number and company name so they appear twice and sorting value desc.
Can please someone help?
SELECT CustomerAccount.AccountNumber,Left(Company.CompanyName,30) as CompanyName,
Convert (VARCHAR (20),SalesOrder.DateEntered,106)as DateEntered,
(SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) as Value
FROM Company
INNER JOIN CustomerAccount ON Company.CompanyID = CustomerAccount.CompanyID
INNER JOIN SalesOrder ON CustomerAccount.CustomerAccountID = SalesOrder.CustomerAccountID
INNER JOIN SalesLine ON SalesOrder.SalesOrderID = SalesLine.SalesOrderID
Inner JOin Product ON SalesLine.ProductID = Product.ProductID
WHERE SalesOrder.DateEntered >= dateadd(dd, datediff(dd, 0, getdate()), -6)
Order By (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate)DESC
Please forgive me if I have posted at the wrong forum.
Thanks,
May 22, 2015 at 4:08 am
yusufm 48726 (5/22/2015)
Hi,I have got a query that I am working on but I can't get it working properly.
I am trying to group account number and company name so they appear twice and sorting value desc.
Can please someone help?
SELECT CustomerAccount.AccountNumber,Left(Company.CompanyName,30) as CompanyName,
Convert (VARCHAR (20),SalesOrder.DateEntered,106)as DateEntered,
(SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) as Value
FROM Company
INNER JOIN CustomerAccount ON Company.CompanyID = CustomerAccount.CompanyID
INNER JOIN SalesOrder ON CustomerAccount.CustomerAccountID = SalesOrder.CustomerAccountID
INNER JOIN SalesLine ON SalesOrder.SalesOrderID = SalesLine.SalesOrderID
Inner JOin Product ON SalesLine.ProductID = Product.ProductID
WHERE SalesOrder.DateEntered >= dateadd(dd, datediff(dd, 0, getdate()), -6)
Order By (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate)DESC
Please forgive me if I have posted at the wrong forum.
Thanks,
well first off you havent got a "GROUP BY" clause.....
secondly , I dont understand what you mean by "group account number and company name so they appear twice "
thirdly....some sample data and expected results will help explain what you are trying to achieve
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 22, 2015 at 4:10 am
When I try to use group by company name or account number then I get the error
Column 'Company.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
May 22, 2015 at 4:15 am
yusufm 48726 (5/22/2015)
When I try to use group by company name or account number then I get the errorColumn 'Company.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
please post the code that is throwing the error
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 22, 2015 at 4:25 am
Hi
I am very new to SQL and appreciate your help.
I am not sure about code but here is the query
Msg 8120, Level 16, State 1, Line 1
Column 'Company.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
May 22, 2015 at 4:30 am
It's as it says in the error message. When you use GROUP BY, every column in the SELECT list must either contain an aggregate function (eg MAX, MIN, COUNT) or appear in the GROUP BY clause. You don't have any aggregate functions in your SELECT list at all.
John
May 22, 2015 at 4:31 am
Is there a way to fix this query?
Thanks,
May 22, 2015 at 4:39 am
yusufm 48726 (5/22/2015)
Is there a way to fix this query?Thanks,
yes......but you still havent given enough detail of what your final results should be
something like this....may help you get started
GROUP BY CustomerAccount.AccountNumber, Company.CompanyName, CONVERT(VARCHAR(20), SalesOrder.DateEntered, 106),
SalesLine.OrderQuantity * SalesLine.SellingPrice / SalesOrder.ExchangeRate
HAVING (SalesOrder.DateEntered >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), - 6))
ORDER BY Value DESC
but I expect that isnt what you want......do you want to SUM the "value", do you want a result for every day, because thats what you will get by including your sales order date in the select/group clause
and you still havent explained the rationale about "I am trying to group account number and company name so they appear twice "
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 22, 2015 at 4:44 am
when I ran the below query
I got this error
Msg 8121, Level 16, State 1, Line 12
Column 'SalesOrder.DateEntered' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
May 22, 2015 at 4:52 am
remove the "HAVING....." line....sorry
but you still havent answered my questions....seems that all you are doing is cutting and pasting without trying to take time to understand the basics of a GROUP BY clause
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 22, 2015 at 4:59 am
Sorry I did cut and paste I ran your query again without having and it runs it but It does give me the desired result where company is listed only once on a particular date and sum that company amount as a whole.
At the moment I am getting the result like this
Account Number Company Name Date Value
1AND Test 21-05-15 10
1AND Test 21-05-15 5
1GAN Good 21-05-15 20
1GAN Good 21-05-15 10
May 22, 2015 at 5:15 am
Thanks for all your help I would try to work something out.
May 22, 2015 at 5:17 am
lets start with some simple example data
your job is to tell us what you would want your results to be on this sample data....edit it accordingly to show all the anomalies you have in your real tables
USE [tempdb]
GO
CREATE TABLE [dbo].[testCompany](
[CompanyID] [int] NULL,
[CompanyName] [varchar](50) NULL
)
CREATE TABLE [dbo].[testCustomerAccount](
[CustomerAccountID] [int] NULL,
[CompanyID] [int] NULL,
[SalesValue] [int] NULL,
[Salesdate] [datetime] NULL
)
INSERT [dbo].[testCompany] ([CompanyID], [CompanyName]) VALUES (1, N'Microsoft')
INSERT [dbo].[testCompany] ([CompanyID], [CompanyName]) VALUES (2, N'Apple')
INSERT [dbo].[testCustomerAccount] ([CustomerAccountID], [CompanyID], [SalesValue], [Salesdate]) VALUES (10, 1, 100, CAST(N'2015-05-01 00:00:00.000' AS DateTime))
INSERT [dbo].[testCustomerAccount] ([CustomerAccountID], [CompanyID], [SalesValue], [Salesdate]) VALUES (15, 1, 50, CAST(N'2015-05-18 00:00:00.000' AS DateTime))
INSERT [dbo].[testCustomerAccount] ([CustomerAccountID], [CompanyID], [SalesValue], [Salesdate]) VALUES (20, 1, 650, CAST(N'2015-05-21 00:00:00.000' AS DateTime))
INSERT [dbo].[testCustomerAccount] ([CustomerAccountID], [CompanyID], [SalesValue], [Salesdate]) VALUES (30, 2, 5000, CAST(N'2015-05-19 00:00:00.000' AS DateTime))
INSERT [dbo].[testCustomerAccount] ([CustomerAccountID], [CompanyID], [SalesValue], [Salesdate]) VALUES (40, 2, 1000, CAST(N'2015-05-14 00:00:00.000' AS DateTime))
INSERT [dbo].[testCustomerAccount] ([CustomerAccountID], [CompanyID], [SalesValue], [Salesdate]) VALUES (50, 2, 400, CAST(N'2015-05-19 00:00:00.000' AS DateTime))
INSERT [dbo].[testCustomerAccount] ([CustomerAccountID], [CompanyID], [SalesValue], [Salesdate]) VALUES (50, 2, 300, CAST(N'2015-05-19 00:00:00.000' AS DateTime))
INSERT [dbo].[testCustomerAccount] ([CustomerAccountID], [CompanyID], [SalesValue], [Salesdate]) VALUES (40, 2, 1000, CAST(N'2015-05-19 00:00:00.000' AS DateTime))
SELECT
C.CompanyID
, LEFT(C.CompanyName , 3) AS Cname
, SUM(CA.SalesValue) AS TotalValue
FROM testCompany AS C
INNER JOIN
testCustomerAccount AS CA ON C.CompanyID = CA.CompanyID
WHERE (CA.Salesdate >= DATEADD(dd , DATEDIFF(dd , 0 , GETDATE()) , - 6))
GROUP BY
C.CompanyID
, LEFT(C.CompanyName , 3)
--ORDER BY
--TotalValue DESC;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 22, 2015 at 5:20 am
Thanks I would try your suggestion and get back to you.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply