Click here to Skip to main content
15,883,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good day to you all, please i need a help with the following:

I get the following error: "column is invalid in the select list because it is not contained in either an aggregate function or the group by clause when i ran the query;
SQL
SELECT CardType, Sum(Amount) AS Total
FROM POSTRANS_Cards
WHERE TransDate='22/11/2015'
GROUP BY CardType

My intention is to get a set of record that show the total value for a specific cardtype on a specified date.

Please any will be appreciated.

Thank you
Frank
Posted
Updated 4-Dec-15 0:55am
v3
Comments
CHill60 4-Dec-15 6:58am    
Are you sure that is the error generated by that code? There is nothing wrong with that query (although I suspect there is a problem with your schema looking at that date)
Frankie-10589807 4-Dec-15 9:16am    
Yes Mr. Chill60, that is the error message i got while at the ssrs, but when i ran the same querry in sql server, this is what i got: "Conversion failed when converting date and / or time from character string"

Thank you Mr. Chill60 for your assistance, actually i am a beginner in this.Thanks again.
CHill60 4-Dec-15 9:27am    
I assume the column TransDate is defined as Date?
In which case try using an unambiguous date format i.e.
WHERE TransDate = '22-NOV-2015'
Once you fix that issue there must be something else in the report that is causing the original error you posted
Frankie-10589807 4-Dec-15 10:10am    
Yes Mr. Chill60, the TransDate field is defined as Date, and using this format:
WHERE TransDate = '22-NOV-2015' in the sql server side, the query ran well. thank you very much, but i also want to ask, is it possible to set a parameter that reference that field from the same query?

I really appreciate your assistance Mr. Chill60, you know, You are just a genius. Thanks again.
Frankie-10589807 4-Dec-15 10:29am    
Hi Mr. Chill60, Wish to inform you that all is now working correctly even when i used a parameter to reference that field "TransDate" at the ssrs side. I thank you so much and more power to your elbow.

Frank.

1 solution

Posting as solution as OP has confirmed it worked.

Use an unambiguous date format so that SQL does not try to convert the string. i.e.
WHERE TransDate = '22-NOV-2015'

In response to the further comments, you can get the details for all of the dates thus:
SQL
SELECT CardType, TransDate, Sum(Amount) AS Total
FROM POSTRANS_Cards
GROUP BY CardType, TransDate
ORDER BY TransDate

Or if you want to run the report for a single date have a look at Lesson 1 on the following tutorial
Adding parameters to reports[^]
 
Share this answer
 
v2
Comments
Richard Deeming 4-Dec-15 11:15am    
The only truly unambiguous date literal format for SQL is yyyyMMdd, with no separators.
WHERE TransDate = '20151122'
Frankie-10589807 4-Dec-15 11:37am    
Ok, thank you so much Mr. Richard. I appreciate.

Frank.
CHill60 8-Dec-15 10:29am    
Very, very good point - not only is this locale-independent (which I wasn't :blush:) but remains unambiguous for datetime in the older versions of SQL Server
Frankie-10589807 15-Dec-15 10:56am    
You are right Mr. Deeming, however, the date format provided by Mr. Chill60 for my challenge work well and to me it reduces the complexity of the initial format i used. These things are machines you know, sometimes they behave in a surprising way. thank you for contributing to the solution for my challenges.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900