asked on

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

Raja Jegan R

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

bjennings

ASKER

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?

Raja Jegan R

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

8080_Diver

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

bjennings

ASKER

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

ralmada

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

8080_Diver

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

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bjennings

ASKER

This worked!!! Thank you so much!!!

bjennings

ASKER

Thank you everyone for your suggestions!!!