?
Solved

Sql Server 2005 GROUP BY clause error message

Posted on 2009-05-01
10
Medium Priority
?
313 Views
Last Modified: 2012-08-13
Hello Everyone,

I am working on a query that will count up all new accounts for a certain time period and give me the total sales for those new accounts.  It would look like this

Total      Sales
5          15,500

The sales are actual based on the difference between 2 fields  (fielddr  fieldcr).  This is what I have so far.

SELECT    COUNT(custname.[custcode]) AS Total, ([DailyCustomerSales].[fielddr] - [DailyCustomerSales].[fieldcr]) AS [Total Sales]
FROM         [CustomerAccount] INNER JOIN
                      custname ON [CustomerAccount].[custkey] = custname.[custkey] INNER JOIN
                      [DailyCustomerSales] ON custname.[custkey] = [DailyCustomerSales].[custkey]
WHERE     (custname.[salesrep] = 'jdoe') AND ([CustomerAccount].[Opendate] >= CONVERT(DATETIME, '2009-04-01', 102)) AND
                      ([CustomerAccount].[Opendate] <= CONVERT(DATETIME, '2009-04-30', 102))
GROUP BY custname.[custcode]

When I run this query I get the following error:

Column 'dailycustomersales.fielddr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column DailyCustomerSales.fieldcr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add it to the group by clause it does not give me the total amount.

Can anyone help me fix this?

Thanks,

Bill
0
Comment
Question by:bjennings
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24281313
This should help you out:
SELECT    COUNT(custname.[custcode]) AS Total, ([DailyCustomerSales].[fielddr] - [DailyCustomerSales].[fieldcr]) AS [Total Sales]
FROM         [CustomerAccount] INNER JOIN
                      custname ON [CustomerAccount].[custkey] = custname.[custkey] INNER JOIN
                      [DailyCustomerSales] ON custname.[custkey] = [DailyCustomerSales].[custkey]
WHERE     (custname.[salesrep] = 'jdoe') AND ([CustomerAccount].[Opendate] >= CONVERT(DATETIME, '2009-04-01', 102)) AND
                      ([CustomerAccount].[Opendate] <= CONVERT(DATETIME, '2009-04-30', 102))
GROUP BY [DailyCustomerSales].[fielddr], [DailyCustomerSales].[fieldcr]

Open in new window

0
 

Author Comment

by:bjennings
ID: 24281404
Hello rrjegan17.....I tried that but I get the following result:
Total sales
1      42.50
1      10.00
2      45.00
1      10.00
3      25.00
xx
xx

Is there a way to count up all the new accounts and add up their sales too for a time periord?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24281465
Hope this helps you out:
SELECT custcode, sum([Total Sales]) AS Total_Sales
from (
SELECT    custname.[custcode], ([DailyCustomerSales].[fielddr] - [DailyCustomerSales].[fieldcr]) AS [Total Sales]
FROM         [CustomerAccount] INNER JOIN
                      custname ON [CustomerAccount].[custkey] = custname.[custkey] INNER JOIN
                      [DailyCustomerSales] ON custname.[custkey] = [DailyCustomerSales].[custkey]
WHERE     (custname.[salesrep] = 'jdoe') AND ([CustomerAccount].[Opendate] >= CONVERT(DATETIME, '2009-04-01', 102)) AND
                      ([CustomerAccount].[Opendate] <= CONVERT(DATETIME, '2009-04-30', 102))
) TEMP
GROUP BY custcode

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:8080_Diver
ID: 24281467
Try the following:

SELECT COUNT(custname.[custcode]) AS Total,
       SUM([DailyCustomerSales].[fielddr] - 
           [DailyCustomerSales].[fieldcr]) AS [Total Sales] 
FROM [CustomerAccount] 
INNER JOIN custname 
   ON [CustomerAccount].[custkey] = custname.[custkey] 
INNER JOIN [DailyCustomerSales] 
   ON custname.[custkey] = [DailyCustomerSales].[custkey] 
WHERE (custname.[salesrep] = 'jdoe') 
  AND ([CustomerAccount].[Opendate] >= CONVERT(DATETIME, '2009-04-01', 102)) 
  AND ([CustomerAccount].[Opendate] <= CONVERT(DATETIME, '2009-04-30', 102)) 
GROUP BY [DailyCustomerSales].[fielddr], [DailyCustomerSales].[fieldcr] 

Open in new window

0
 

Author Comment

by:bjennings
ID: 24282537
rrjegan17...IT is getting closer it will now give me a each new account code and there sales

account

1123    10,000
1236    15,000
5601    12,000

Is there a way to get just a count and a total sales?

Thanks,

Bill
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24282659
What about this?
SELECT COUNT(custname.[custcode]) AS Total,
       SUM([DailyCustomerSales].[fielddr] - 
           [DailyCustomerSales].[fieldcr]) AS [Total Sales] 
FROM [CustomerAccount] 
INNER JOIN custname 
   ON [CustomerAccount].[custkey] = custname.[custkey] 
INNER JOIN [DailyCustomerSales] 
   ON custname.[custkey] = [DailyCustomerSales].[custkey] 
WHERE (custname.[salesrep] = 'jdoe') 
  AND ([CustomerAccount].[Opendate] >= CONVERT(DATETIME, '2009-04-01', 102)) 
  AND ([CustomerAccount].[Opendate] <= CONVERT(DATETIME, '2009-04-30', 102))

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24282873
Are you sure that you want just the counts and the totals?  
After all, how will you know what the data means or what it really relates to without at least the Customer Account Number?
This is one of those times when you, of course, know what  you are going to do with the data but I have to wonder what the utility of the data without any identification would be.

SELECT custcode,
       COUNT(custname.[custcode]) AS Total,
       SUM([DailyCustomerSales].[fielddr] - 
           [DailyCustomerSales].[fieldcr]) AS [Total Sales] 
FROM [CustomerAccount] 
INNER JOIN custname 
   ON [CustomerAccount].[custkey] = custname.[custkey] 
INNER JOIN [DailyCustomerSales] 
   ON custname.[custkey] = [DailyCustomerSales].[custkey] 
WHERE (custname.[salesrep] = 'jdoe') 
  AND ([CustomerAccount].[Opendate] >= CONVERT(DATETIME, '2009-04-01', 102)) 
  AND ([CustomerAccount].[Opendate] <= CONVERT(DATETIME, '2009-04-30', 102)) 
GROUP BY custcode

Open in new window

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1600 total points
ID: 24286146
Try this one out:

Remove the keyword distinct in the first line as required
SELECT count( distinct custcode) as total_count, sum([Total Sales]) AS Total_Sales
from (
SELECT    custname.[custcode], ([DailyCustomerSales].[fielddr] - [DailyCustomerSales].[fieldcr]) AS [Total Sales]
FROM         [CustomerAccount] INNER JOIN
                      custname ON [CustomerAccount].[custkey] = custname.[custkey] INNER JOIN
                      [DailyCustomerSales] ON custname.[custkey] = [DailyCustomerSales].[custkey]
WHERE     (custname.[salesrep] = 'jdoe') AND ([CustomerAccount].[Opendate] >= CONVERT(DATETIME, '2009-04-01', 102)) AND
                      ([CustomerAccount].[Opendate] <= CONVERT(DATETIME, '2009-04-30', 102))
) TEMP

Open in new window

0
 

Author Closing Comment

by:bjennings
ID: 31576972
This worked!!!  Thank you so much!!!
0
 

Author Comment

by:bjennings
ID: 24296112
Thank you everyone for your suggestions!!!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 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