problem with group by

hello all,

i am using odbc connection and it works fine, but i'm having trouble with select statement using group by. i want to display selected fields from 4 different table and it will display by grouping events.t_id.

this my scripts

$query = " select events.t_id, events.e_status, events.e_assignedto, events.e_id, ";
$query .= " events.e_timestamp, tmpeid.e_id, category.c_name, ";
$query .= " ticket.t_summary, ticket.t_category, ";
$query .= " ticket.t_user, ticket.t_priority, ticket.t_timestamp_opened, ";
$query .= " ticket.t_id2, ticket.t_id, COUNT (*)";
$query .= " FROM events, tmpeid, category, ticket ";
$query .= " WHERE ticket.t_id = events.t_id ";
$query .= " AND events.e_id=tmpeid.e_id";
$query .= " GROUP BY events.t_id";
$query .= " HAVING COUNT(events.t_id) >= 1 ";

this is error msg that i've found

Warning: SQL error: [Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression , SQL state S1000 in SQLExecDirect in c:\apache\htdocs\scripts

can anybody solve for me???Hello,

the problem is, that you use GROUP BY AND COUNT and do not specifiy what Oracle has to do with all the other fields in your SELECT statement.
f.e

SELECT grade FROM scott.salgrade GROUP BY grade (works fine)

SELECT grade, COUNT(losal) FROM scott.salgrade GROUP BY grade
(also works fine, cause grade will be grouped and in every grouped record you will get a count of losal)

SELECT grade, COUNR(losal), hisal FROM scott.salgrade GROUP BY grade

(will raise an exception - cause Oracle does not know what to do with hisal in the grouped record)

a

SELECT grade, COUNR(losal), SUM(hisal) FROM scott.salgrade GROUP BY grade

(works also fine)

So ... what you have to do is to kick out all the fields that has no group or agregate command and run the statement again.

or ...

group every field in the list

Hope this helps

Manfred Peter
(Alligator Company)
http://www.alligatorsql.comsql

Problem with date and time function

I am using MSSQL 2000. I building a search page in which all jobs listed since the past seven days will be selected sequentially. The dropdownlist goes from 0-7, if i select 1 it should give yestedays jobs , if i select 2 it should give all the jobs within two days and so on upto seven days. I am using this Sql Statement but its not working out. Has anyone got any idea about this problem?
SELECT * FROM TableName WHERE ColumnName BETWEEN DATEADD(dd,-1, GETDATE( )) AND GETDATE( )
Thanx in AdvanceYou probably want a stored proc that would look something like this:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12), GETDATE()))
SET @.EndDate = DATEADD(second, -1, DATEADD(day, @.Days + 1, CONVERT(VARCHAR(12), GETDATE())))

SELECT * FROM TableName WHERE ColumnName BETWEEN @.StartDate AND @.EndDate

|||'Cept for that I didn't read your post carefully enough. To getthe jobs from all previous days, instead of a single selected day, it'dlook something like this:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12), GETDATE()))
SET @.EndDate = DATEADD(second, -1, CONVERT(VARCHAR(12), GETDATE()))

SELECT * FROM TableName WHERE ColumnName BETWEEN @.StartDate AND @.EndDate

|||Xander, I suggest that this might work better:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.Now
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SELECT @.Now = GETDATE()
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12),@.Now))
SET @.EndDate = CONVERT(char(8), @.Now,112)

SELECT * FROM TableName WHERE ColumnName >= @.StartDate AND ColumnName < @.EndDate

What you suggested could give unexpected results if it is a fewmilliseconds before midnight. What I have done takes millisecondsout of the equation. Also, it's good to take a snapshot of thecurrent time in the beginning and use that static date/time for theremainder of the procedure. This way you don't need to worryabout the stored procedure crossing date boundaries in the middle ofexecution.
|||Thank you very much for all your replies.
I tried the above idea and worked perfectly.
The problem which was bugging me finally got solved.
Thanx once again