Need help with query errors

[samcham]

My table (GlAccountAmounts) has the following columns:

SourceID
DateTime
AccountID
AccountClass
AccountClassType
AccountControlAccount
AccountCorporation
AccountCorporationName
AccountDescription
Balance
Credit
Debit
RowUpdateDateTime

I want to show totals for the Debit and Credit columns, by DateTime, for AccountClass = 3 only.

I tried the following query:

SELECT DateTime, AccountID, AccountClass, Credit, Debit
FROM GlAccountAmounts
WHERE AccountClass = 3
GROUP BY DateTime

And I got this error message:

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Column 'GlAccountAmounts.AccountID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

------------------------------
ADDITIONAL INFORMATION:

Column 'GlAccountAmounts.AccountID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (Microsoft SQL Server, Error: 8120)


Can someone please shed some light on what I'm doing wrong here? I'm new at this, and it's rather frustrating...a bit like trying to tell a joke in a foreign langauge.

I'm a real novice at writing queries, so please forgive the "noob" questions...

 

[gmmastros]

There are certain functions in SQL Server that are called aggregates, like Sum, Count, Avg, etc... These functions can return values in a query, but there are rules associated with using aggregates. Specifically, when you use an aggregate, all the columns returned in the select clause must also be in the group by clause or contained in an aggregate.

Let's take a look at your query:

[tt]
SELECT [!]DateTime, AccountID, AccountClass, Credit, Debit[/!]
FROM GlAccountAmounts
WHERE AccountClass = 3
GROUP BY [!]DateTime[/!]
[/tt]

You are returning 5 columns in the select clause and only have one column in the group by clause.

When you use a group by clause, you are guaranteed to get exactly one row in the output for each distinct combination of columns in the group by. In the select clause, aggregates are grouped by the columns in the group by query.

So, if you want the sum of the credit and the sum of the debit columns, you would use the SUM function for those columns, and then you would list the other 3 columns in the group by clause. So, for each combination of date, AccountId, and account class that exists in the table, you will get a row in the output. If this is what you want...

SELECT [!]DateTime, AccountID, AccountClass, Sum(Credit) As TotalCredit, Sum(Debit) As TotalDebit[/!]
FROM GlAccountAmounts
WHERE AccountClass = 3
GROUP BY [!]DateTime, AccountId, AccountClass[/!]

The query I show above should run without errors, but it probably will not return the data you expect either. You have a DateTime column which probably holds date AND time. Since you are grouping by the DateTime column, you will likely get multiple rows in the output for each AccountId, AccountClass combination because the times are likely to be different. If you want daily totals, you will need to remove the time component. Like this...

SELECT [!]DateAdd(Day, DateDiff(Day, 0, DateTime), 0) As TheDate[/!], AccountID, AccountClass, Sum(Credit) As TotalCredit, Sum(Debit) As TotalDebit
FROM GlAccountAmounts
WHERE AccountClass = 3
GROUP BY [!]DateAdd(Day, DateDiff(Day, 0, DateTime), 0)[/!], AccountId, AccountClass

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[samcham]

George:

Thanks very much for the quick reply. The query did indeed work. I didn't realize that I needed to include other fields in the GROUP BY statement. (This is all part of my learning curve.)

Now, I have another question.

This is a report that I need to run every day, and it needs to show daily totals for each day of the current month. My data is always a day behind, so on the 1st day of each month, it must report on all days of the previous month. Starting on the 2nd of the month, it needs to begin reporting on the current month, beginning with the 1st.

Any thoughts on query logic that can accomplish this?

(Also, can you recommend a good reference book that will help me learn how to write T-SQL queries?)

Thanks again!

-----------------------------
Using microsoft SQL Server 2005
(I'm a beginner at this, so please forgive the "noob" questions...)

 

[gmmastros]

This is a report that I need to run every day

How do you run the reports? Did you write your own application? Are you using crystal reports? Something else?

The reason I ask is....

You could write your date range logic in to the query itself, but it would be better to pass the 'ReportDate' in to the query. More specifically, I would recommend that you create a stored procedure that accepts a single 'ReportDate' parameter. Then, when you run the report, you pass in the report date. If this happens to be the system date, so be it. But, if you create the stored procedure method like I am suggesting, you will be able to run the report for any month, at any time.

If this is acceptable to you, and you would like me to show you how.... I can do that. Specifically, I can show you how to do the database stuff. It would be up to you to figure out how to call a stored procedure (instead of a query) and pass a parameter from your current reporting tool. This part should be pretty easy to figure out, but it's something I probably won't be able to help you with.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[samcham]

George:

I did forget to mention that I'm using SQL Server Reporting Services (2005). I took a 3-day class in SSRS, and can use the program. But it's useless without the basic T-SQL query, and that's where I'm struggling.

I haven't found anything in my SSRS materials that sheds any light on this date issue, which is why i'm wondering if there's a way to handle it in the query.

I'll program SSRS to run the report daily via an automatic snapshot, and distribute it via e-mail subscription. As such, the time frame cannot be a user input item.

I can use the system date, but I do have this issues of what to do with the first of the month, when it needs to run for the previous month.

Thanks.

-----------------------------
Using microsoft SQL Server 2005
(I'm a beginner at this, so please forgive the "noob" questions...)

 

[gmmastros]

To create a stored procedure, you'll need to use SQL Server Management Studio. Open that tool, drill down to your database, and then open a new query window.

Copy/paste this code:

Create Procedure dbo.MyMonthlyReport
  @ReportDate DateTime = NULL
As
SET NOCOUNT ON

If @ReportDate Is NULL
	Set @ReportDate = GetDate()

Declare @StartDate DateTime
Declare @EndDate DateTime

Select @StartDate = DateAdd(Month, DateDiff(Month, 0, @ReportDate), 0)

If Day(@ReportDate) = 1
	Set @StartDate = DateAdd(Month, -1, @StartDate)

Set @EndDate = DateAdd(Month, 1, @StartDate)

SELECT DateAdd(Day, DateDiff(Day, 0, DateTime), 0) As TheDate, AccountID, AccountClass, Sum(Credit) As TotalCredit, Sum(Debit) As TotalDebit
FROM   GlAccountAmounts
WHERE  AccountClass = 3
       And DateTime >= @StartDate
       And DateTime < @EndDate
GROUP BY DateAdd(Day, DateDiff(Day, 0, DateTime), 0), AccountId, AccountClass

Let's examine this code piece by piece.

[tt][blue]
Create Procedure dbo.MyMonthlyReport
@ReportDate DateTime = NULL
[/blue][/tt]

This part is creating the stored procedure. The name is 'MyMonthlyReport' (you should probably use something better). There is one parameter to this stored procedure named @ReportDate. This parameter is a DateTime data type. This means that the parameter value (when you call the procedure) MUST be a valid Date/Time value or NULL. Notice the [!]= NULL[/!] part. This means the parameter is optional. You can call the procedure with a parameter or without. If you call the procedure without a parameter, the @ReportDate value will be null.

[tt][blue]
If @ReportDate Is NULL
Set @ReportDate = GetDate()
[/blue][/tt]

If the @ReportDate parameter is NULL, then set it to the server's current date and time.

[tt][blue]
Declare @StartDate DateTime
Declare @EndDate DateTime
[/blue][/tt]

Declare a couple of local variables.

[tt][blue]
Select @StartDate = DateAdd(Month, DateDiff(Month, 0, @ReportDate), 0)
[/blue][/tt]

This line of code removes time and day of month from the report date variable and assigns it to the @StartDate variable.

[tt][blue]
If Day(@ReportDate) = 1
Set @StartDate = DateAdd(Month, -1, @StartDate)
[/blue][/tt]

If the Day of the month = 1, subtract one month from the start date.

[tt][blue]
Set @EndDate = DateAdd(Month, 1, @StartDate)
[/blue][/tt]

Set the end date to be 1 month greater than the start date

[tt][blue]
WHERE AccountClass = 3
And DateTime >= @StartDate
And DateTime < @EndDate
[/blue][/tt]

Limit the query so that only DateTime greater than or equal to @StartDate are returned, and DateTime less than @EndDate.

After copy/paste, you can press F5 to run the query. It will not appear to do anything, but it did. It actually created a stored procedure in your database. To test the stored procedure, open a new query window and test it, like this...

Exec MyMonthlyReport
Exec MyMonthlyReport '20100514'
Exec MyMonthlyReport '20100501' -- First day of may, should report april

Then, all you need to do is to get SSRS to run the procedure for you.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[samcham]

George:

Yes, I think it does. I'm heading out for the weekend, but will start working with this on Monday. Thanks again for all your help!

-----------------------------
Using microsoft SQL Server 2005
(I'm a beginner at this, so please forgive the "noob" questions...)

 

[gmmastros]

You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom