Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
It's possible: Each column = result of SELECT sub-query ? |
Wed, Jan 24 2007 9:46 AM | Permanent Link |
Max Terentiev | 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 ! |
Wed, Jan 24 2007 10:10 AM | Permanent Link |
"Robert" | "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 |
Wed, Jan 24 2007 10:13 AM | Permanent Link |
Chris Erdal | 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) |
Wed, Jan 24 2007 10:14 AM | Permanent Link |
Chris Erdal | "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) |
Wed, Jan 24 2007 11:29 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jan 24 2007 12:12 PM | Permanent Link |
"Robert" | "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 |
Sun, Feb 11 2007 2:32 AM | Permanent Link |
JS549 | 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 |
Sun, Feb 11 2007 10:44 AM | Permanent Link |
"Robert" | "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 |
This web page was last updated on Friday, May 3, 2024 at 08:07 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |