Combining 2 recordsets in one query possible?

[Guest]

Hello,

I've simplified my 2 queries. Is it possible to combine the 2 queries to get the total?

I use in my query window (in Dreamweaver)

(SELECT COUNT(*) FROM table_1 WHERE field_1="X") + (SELECT COUNT(*) FROM table_2 WHERE field_1="Y") AS TOTAL

When I click 'TEST', I got a message that SQL Syntax is wrong.
Any ideas?

Regards.

[Newsgroup_User]

.oO(cosmobe)

> I've simplified my 2 queries. Is it possible to combine the 2 queries to get
>the total?

Yes.

> I use in my query window (in Dreamweaver)
>
> (SELECT COUNT(*) FROM tabel_1 WHERE veld_1="X") + (SELECT COUNT(*) FROM
>tabel_2 WHERE veld_1="Y") AS TOTAL

SELECT COUNT(*) FROM table_1 ...
UNION
SELECT COUNT(*) FROM table_2 ...

Micha

[Guest]

Just tried this and I get the count from the 2 queries in my query result-pane

line 1: 4
line 2: 6

How can I calculate the SUM of those the 2 count queries within one (union)-query? Can I do this?

[Newsgroup_User]

.oO(cosmobe)

>Just tried this and I get the count from the 2 queries in my query result-pane
>
>line 1: 4
>line 2: 6

OK, I misread your question. My fault.

>How can I calculate the SUM of those 2? Can I do this?

Sure. I would probably do it in the script, but you can also do it with
SQL already. One way would be to use sub selects:

SELECT
(SELECT COUNT(*) FROM table_1 ...) +
(SELECT COUNT(*) FROM table_2 ...)
AS total;

Micha

[Guest]

Yes, it's working now.
Thanks!

[Newsgroup_User]

Or you could do it like this using derived tables:

SELECT (a.Total1 + b.Total2) as 'TOTAL' FROM
(SELECT COUNT(*) as Total1 FROM tabel_1 WHERE veld_1='X') a,
(SELECT COUNT(*) as Total2 FROM tabel_2 WHERE veld_1='Y') b


"cosmobe" <webforumsuser@macromedia.com> wrote in message
news:fe2e4j$oq2$1@forums.macromedia.com...
> Yes, it's working now.
> Thanks!
>
>