SQLITE QUERY ?

hello guys sorry i’m still beginner and i need help for this query

Dim rs as RecordSet=dbt1.SQLSelect(“SELECT ID,NAME(SELECT SUM(BILLAMAUNT) SUM(PAID) SUM(DISCAUNT) FROM TRANSACTIONS WHERE ID=ID) FROM AGENTS”)

The Result is RS NIL ?

a) did you check for errors or did you blindly assume the query was ok?
b) WHERE ID=ID will alway be true… unless you alias them to indicate which table there are from A.ID=B.ID
c) you are most likely missing a comma
d) you should alias all the SUM()
e) you are missing commas in your subselect
f) did you mis-spell BILLAMOUNT and DISCOUNT?

SELECT  a.ID,
            a.NAME,
(SELECT SUM(BILLAMAUNT) as billamount,
              SUM(PAID) as paid,
 SUM(DISCAUNT)  as discount
FROM TRANSACTIONS b
 WHERE a.ID=a.ID) 
FROM AGENTS  

thx dave , but i still got this error message “only a single result allowed for a SELECT that is part of an expression” ?

If I do understand correctly what you are trying to do, here is a full sample:


-- Just one example for understanding — Does not match exactly your fields

PRAGMA foreign_keys = false;

-- ----------------------------
--  Table structure for AGENTS
-- ----------------------------
DROP TABLE IF EXISTS "AGENTS";
CREATE TABLE "AGENTS" (
	 "id" INTEGER NOT NULL,
	 "name" TEXT,
	PRIMARY KEY("id")
);

-- ----------------------------
--  Records of AGENTS
-- ----------------------------
BEGIN;
INSERT INTO "AGENTS" VALUES (1, 'Rick');
COMMIT;

-- ----------------------------
--  Table structure for TRANSACTIONS
-- ----------------------------
DROP TABLE IF EXISTS "TRANSACTIONS";
CREATE TABLE "TRANSACTIONS" ( "id" INTEGER NOT NULL, "agentId" INTEGER NOT NULL, "billAmount" real, "paid" real, PRIMARY KEY ("id")  );

-- ----------------------------
--  Records of TRANSACTIONS
-- ----------------------------
BEGIN;
INSERT INTO "TRANSACTIONS" VALUES (1, 1, 1000.0, 100.0);
INSERT INTO "TRANSACTIONS" VALUES (2, 1, 1000.0, 400.0);
COMMIT;

-- ----------------------------
--  Indexes structure for table TRANSACTIONS
-- ----------------------------
CREATE INDEX "idx_agentid" ON TRANSACTIONS ("agentId");

PRAGMA foreign_keys = true;

-- -- -- -- -- -- Now the fun -- -- -- -- -- -- 

SELECT AGENTS.id AS agentId, 
	AGENTS.name, 
	SUM(TRANSACTIONS.paid) AS paid, 
	SUM(TRANSACTIONS.billAmount) AS billAmount
FROM AGENTS INNER JOIN TRANSACTIONS ON AGENTS.id = TRANSACTIONS.agentId
GROUP BY AGENTS.id

oh thank you very much Rick it work very well