Query help 1

[Bku]

Hi,
I'm a newbie. I tried the following in Access and it worked, but then I tried it in Interbase and it didn't like the SUM lines. Is there an equivalent for this statement? The query is from one table only. Thanks.
----------------------------------------------

SELECT Task.OwnerID,
SUM(IsComplete = -1) as CompleteTrue,
SUM(IsComplete = 0) as CompleteFalse
FROM Task
WHERE TaskType = 2
GROUP BY OwnerID

 

[cheikh1]

hi
i don't understand the query!!
can you tell me
what you want to do
in english language not in Access.

 

[Bku]

SELECT Task.OwnerID,
SUM(IsComplete = -1) as CompleteTrue,
SUM(IsComplete = 0) as CompleteFalse
FROM Task
WHERE TaskType = 2
GROUP BY OwnerID


Sorry about that. I'm trying to SUM the number of True/False responses in the 'IsComplete' field for each 'OwnerID'. My sum statement is trying to get the 'IsComplete' field where it is True by putting the condition "= -1" in the SUM(). It works in Access, but not Interbase.

 

[MartijnTonies]

Hi,

SUM doesn't do comparisons. My best bet is something like this:

SELECT T.OwnerID,
(select SUM(t1.IsComplete) from task t1 where t1.ownerid = t.ownerid and t1.tasktype = 2 and t1.iscomplete = -1) as CompleteTrue,
(select SUM(t2.IsComplete) from task t2 where t2.ownerid = t.ownerid and t2.tasktype = 2 and t2.iscomplete = 0) as CompleteFalse,
FROM Task t
WHERE t.TaskType = 2
GROUP BY t.OwnerID


Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[cheikh1]

the following query Will
give you an answer like the following

ownerid iscompleete f_1
12 0 2
12 -1 3
13 0 8
14 -1 7

query
select OwnerId,IsCompleete,count(*)
from Task
where TaskType=2
group by OwnerId,IsCompleete;

 

[Bku]

MartijnTonies, I ran your query in Interactive SQL but this is what I get:

Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, char 1


I'm begining to think there is something wrong with my IBConsole ver 1.0.0.336

 

[MartijnTonies]

This error is coming from the server, not your IBConsole. It's probably some typo in my statement ...

Can you post the table metadata (CREATE TABLE)?

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[Bku]

Thanks for all your help. Here it is.


/* Domain definitions */
CREATE DOMAIN "T_YESNO" AS INTEGER
DEFAULT 0
CHECK((VALUE IS NULL) OR (VALUE IN (0,-1)));

/* Table: TASK, Owner: MSUSER */

CREATE TABLE "TASK"
(
"TASKID" INTEGER NOT NULL,
"DATESTART" TIMESTAMP,
"DATEDUE" TIMESTAMP,
"SUBJECT" VARCHAR(200),
"AUTOSUBJECT" "T_YESNO",
"NOTES" VARCHAR(2000),
"STATUS" INTEGER,
"TASKTYPE" INTEGER,
"PRIORITY" INTEGER,
"PERCENTCOMPLETE" INTEGER,
"ISCOMPLETE" "T_YESNO",
"OWNERID" INTEGER,
"OWNERNAME" VARCHAR(100),
"TEAMID" INTEGER,
"TEAMNAME" VARCHAR(100),
"STOCKNUMBER" VARCHAR(50),
"CUSTOMERID" INTEGER,
"CUSTOMERNAME" VARCHAR(200),
"UPCARDID" INTEGER,
"UPCARDNAME" VARCHAR(200),
"MAILMERGETEMPLATEID" INTEGER,
"MAILMERGETEMPLATENAME" VARCHAR(200),
"REPORTID" INTEGER,
"REPORTNAME" VARCHAR(200),
"DATECREATED" TIMESTAMP,
"CREATEDBYID" INTEGER,
"DATEMODIFIED" TIMESTAMP,
"MODIFIEDBYID" INTEGER,
"DATECOMPLETED" TIMESTAMP,
"COMPLETEDBYID" INTEGER,
"APPOINTMENTTIME" TIMESTAMP,
"DURATIONMINUTES" INTEGER,
"HASREMINDER" "T_YESNO",
"HASBEENREMINDED" "T_YESNO",
"REMINDTIME" INTEGER,
"REMINDERTIMESTAMP" TIMESTAMP,
"OUTCOME" INTEGER,
"ACTIONID" INTEGER,
"CANCANCEL" "T_YESNO",
"PARENTID" INTEGER,
"ISRECURRING" "T_YESNO",
"ISTEMPLATE" "T_YESNO",
"ISAUTOSCHEDULED" "T_YESNO",
"ISINACTIVE" "T_YESNO",
"SALESFORCE" INTEGER,
"CUST_QUERY" BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
"MS_LAST_WRITE" TIMESTAMP,
"CUSTOMERCOUNT" INTEGER,
"WORKFLOWID" INTEGER,
"CUSTOMERHOMEPHONE" VARCHAR(100),
"ISSERVICEREMINDER" "T_YESNO",
"ISSOLDCUSTOMER" "T_YESNO",
"BDCREPID" INTEGER,
"BDCREPNAME" VARCHAR(100),
CONSTRAINT "PK_TASK" PRIMARY KEY ("TASKID&quot
);
SET TERM ^ ;


/* Triggers only will work for SQL triggers */

CREATE TRIGGER "BI_TASK" FOR "TASK"
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN "TASK".MS_LAST_WRITE = 'NOW'; IF ("TASK"."TASKID" IS NULL) THEN "TASK"."TASKID" = GEN_ID("GEN_TASK", 1); END
^

CREATE TRIGGER "BU_TASK" FOR "TASK"
ACTIVE BEFORE UPDATE POSITION 0
AS BEGIN "TASK".MS_LAST_WRITE = 'NOW'; END
^

COMMIT WORK ^
SET TERM ;^

 

[MartijnTonies]

ok - got it:

SELECT T.OwnerID,
(select count(t1.IsComplete) from task t1 where t1.ownerid = t.ownerid and t1.tasktype = 2 and t1.iscomplete = -1) as CompleteTrue,
(select count(t2.IsComplete) from task t2 where t2.ownerid = t.ownerid and t2.tasktype = 2 and t2.iscomplete = 0) as CompleteFalse
FROM Task t
WHERE t.TaskType = 2
GROUP BY t.OwnerID

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[Bku]

That worked! thanks, you were very helpful. I learn a lot from reading stuff here.