• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Sql Server 2005 GROUP BY clause error message

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
bjennings
Asked:
bjennings
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
bjenningsAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
8080_DiverCommented:
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
 
bjenningsAuthor Commented:
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
 
ralmadaCommented:
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
 
8080_DiverCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
bjenningsAuthor Commented:
This worked!!!  Thank you so much!!!
0
 
bjenningsAuthor Commented:
Thank you everyone for your suggestions!!!
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.

Join & Write a Comment

Featured Post

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.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now