×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Query help

Query help

Query help

(OP)
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

RE: Query help

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

RE: Query help

(OP)
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.

RE: Query help

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

RE: Query help

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;

RE: Query help

(OP)
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

RE: Query help

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

RE: Query help

(OP)
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")
);
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 ;^

RE: Query help

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

RE: Query help

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close