Hello,

I want show result of this queries in single table:

SELECT COUNT(ID) AS TotalProbes FROM "Probes" WHERE ProbeDate='2006-10-10';

SELECT COUNT(ID) AS PaidProbes FROM "Probes" WHERE ProbeDate='2006-10-10' AND PaidType=1;

SELECT COUNT(ID) AS MensCount FROM "Probes" WHERE ProbeDate='2006-10-10' AND Sex=1;

How i can join this queries into big singe query ? As result i need this table:
______________________________
|TotalProbes|PaidProbes|MensCount|
     100             25              15
------------------------------------------

I was try to write this:

select
 (SELECT COUNT(ID) AS TotalProbes FROM "Probes" WHERE TestDate='2006-10-10'),
 (SELECT COUNT(ID) AS PaidProbes FROM "Probes" WHERE TestDate='2006-10-10' AND PaidType=1),
 (SELECT COUNT(ID) AS MensCount FROM "Probes" WHERE TestDate='2006-10-10' AND Sex=1)
from "Probes"

But it's not works

Thanx for help !

"Max Terentiev" <maxterentiev@mail.ru> wrote in message
news:A869A69E-0B13-49EE-9A84-15610ABA27F0@news.elevatesoft.com...
> Hello,
>
> I want show result of this queries in single table:

SELECT SUM(1) AS TotalProbes.
SUM(IF(PaidType=1 THEN 1 ELSE 0)) AS PaidProbes
SUM(IF(Sex=1 THEN 1 ELSE 0)) AS MensCount
FROM "Probes" WHERE ProbeDate='2006-10-10'

Robert

Max Terentiev <maxterentiev@mail.ru> wrote in
news:A869A69E-0B13-49EE-9A84-15610ABA27F0@news.elevatesoft.com:

> Hello,
>
> I want show result of this queries in single table:
>
> SELECT COUNT(ID) AS TotalProbes FROM "Probes" WHERE
> ProbeDate='2006-10-10';
>
> SELECT COUNT(ID) AS PaidProbes FROM "Probes" WHERE
> ProbeDate='2006-10-10' AND PaidType=1;
>
> SELECT COUNT(ID) AS MensCount FROM "Probes" WHERE
> ProbeDate='2006-10-10' AND Sex=1;
>
> How i can join this queries into big singe query ? As result i need
> this table: ______________________________
>|TotalProbes|PaidProbes|MensCount|
>       100             25              15
> ------------------------------------------

Keeping to fairly standard SQL, you could do something like this:

SELECT PaidType, Sex
INTO "/memory/tmp1"
FROM "Probes" WHERE ProbeDate='2006-10-10';


SELECT COUNT(*) NbProbes, 0 PdType, 0 Sx
INTO "/memory/tmp2"
FROM "/memory/tmp1";

INSERT INTO "/memory/tmp2"
SELECT 0 , COUNT(*) , 0
FROM "/memory/tmp1"
WHERE PaidType=1;

INSERT INTO "/memory/tmp2"
SELECT 0 , 0 , COUNT(*)
FROM "/memory/tmp1"
WHERE Sex=1;

SELECT SUM(NbProbes) TotalProbes, SUM(PdType) PaidProbes,
      SUM(Sx) MensCount
FROM "/memory/tmp2";

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in news:345B8407-
7A41-4473-B74D-ED7C0CC79848@news.elevatesoft.com:

>
> "Max Terentiev" <maxterentiev@mail.ru> wrote in message
> news:A869A69E-0B13-49EE-9A84-15610ABA27F0@news.elevatesoft.com...
>> Hello,
>>
>> I want show result of this queries in single table:
>
> SELECT SUM(1) AS TotalProbes.
> SUM(IF(PaidType=1 THEN 1 ELSE 0)) AS PaidProbes
> SUM(IF(Sex=1 THEN 1 ELSE 0)) AS MensCount
> FROM "Probes" WHERE ProbeDate='2006-10-10'
>
> Robert
>
>

So-o-o much simpler with these new-fangled IFs!

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

Max,

Robert answered your question perfectly, as always, but I have one
question - is this application used by aliens from outer space ?

--
Tim Young
Elevate Software
www.elevatesoft.com

"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns98C2A3EE8E85F14torcatis@64.65.248.118...
>
> So-o-o much simpler with these new-fangled IFs!
>

Using SUM(1 or zero) and IF instead of COUNT  gives you a lot of
flexibility.

R

Max Terentiev <maxterentiev@mail.ru> wrote:

Hello,

I want show result of this queries in single table:

SELECT COUNT(ID) AS TotalProbes FROM "Probes" WHERE ProbeDate='2006-10-10';

SELECT COUNT(ID) AS PaidProbes FROM "Probes" WHERE ProbeDate='2006-10-10' AND PaidType=1;

SELECT COUNT(ID) AS MensCount FROM "Probes" WHERE ProbeDate='2006-10-10' AND Sex=1;

How i can join this queries into big singe query ? As result i need this table:
______________________________
|TotalProbes|PaidProbes|MensCount|
     100             25              15
------------------------------------------

I was try to write this:

select
 (SELECT COUNT(ID) AS TotalProbes FROM "Probes" WHERE TestDate='2006-10-10'),
 (SELECT COUNT(ID) AS PaidProbes FROM "Probes" WHERE TestDate='2006-10-10' AND PaidType=1),
 (SELECT COUNT(ID) AS MensCount FROM "Probes" WHERE TestDate='2006-10-10' AND Sex=1)
from "Probes"

But it's not works

Thanx for help !

Try

select a.TotalProbes, b.PaidProbes, c.Menscount
from (SELECT  COUNT(ID) AS TotalProbes FROM "Probes" WHERE TestDate='2006-10-10') a,
(SELECT COUNT(ID) AS PaidProbes FROM "Probes" WHERE TestDate='2006-10-10' AND PaidType=1) b,
(SELECT COUNT(ID) AS MensCount FROM "Probes" WHERE TestDate='2006-10-10' AND Sex=1) c

Or similar

"JS549" <john_standen@hotmail.com> wrote in message
news:C9A3A09E-4D6B-49A3-AE89-61E1CA4CA8BE@news.elevatesoft.com...
> Max Terentiev <maxterentiev@mail.ru> wrote:
>
> Hello,
>
> I want show result of this queries in single table:
>
> SELECT COUNT(ID) AS TotalProbes FROM "Probes" WHERE
> ProbeDate='2006-10-10';
>
> SELECT COUNT(ID) AS PaidProbes FROM "Probes" WHERE ProbeDate='2006-10-10'
> AND PaidType=1;

1. Use IF and SUM instead of COUNT

SELECT COUNT(ID) AS TotalProbes FROM "Probes" ,
 SUM(IF( PaidType = 1 then 1 else 0)) as PaidProbes
FROM "Probes" WHERE TestDate='2006-10-10'

2, It helps to check the newsgroups to see if this has been answered before.

Robert