Обсуждение: Where clause
Hello, I have a table called tracking, with a contactid varchar, click bool, view bool and cid varchar. I would like to put the following into one single query if possible: // Number of clicks select cid,count(distinct contactid) from tracking where click = true group by cid; // Number of views select cid,count(distinct contactid) from tracking where view = true group by cid; I guess I have to put where (click = true or view = true) - and differentiate them in the SELECT target.. ? Many thanks for any input, Mike
am Tue, dem 26.06.2007, um 10:24:05 +0200 mailte Michael Landin Hostbaek folgendes: > Hello, > > I have a table called tracking, with a contactid varchar, click bool, > view bool and cid varchar. > > I would like to put the following into one single query if possible: > > // Number of clicks > select cid,count(distinct contactid) from tracking where click = > true group by cid; > > // Number of views > select cid,count(distinct contactid) from tracking where view = > true group by cid; > > I guess I have to put where (click = true or view = true) - and > differentiate them in the SELECT target.. ? *untested* select cid, sum(case when click = true then 1 else 0 end), sum(case when view = true then 1 else 0 end) from ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer (andreas.kretschmer) writes: > *untested* > > select cid, sum(case when click = true then 1 else 0 end), sum(case when > view = true then 1 else 0 end) from ... > Thanks, but I need the DISTINCT contactid - I don't want the same contactid counted twice. Mike
Στις Τρίτη 26 Ιούνιος 2007 12:44, ο/η Michael Landin Hostbaek έγραψε: > A. Kretschmer (andreas.kretschmer) writes: > > *untested* > > > > select cid, sum(case when click = true then 1 else 0 end), sum(case when > > view = true then 1 else 0 end) from ... > > Thanks, but I need the DISTINCT contactid - I don't want the same > contactid counted twice. > Something like SELECT distinct cid,(select count(distinct t1.contactid) from tracking t1 where t1.view and t1.cid=tglob.cid) as countviews,(select count(distinct t2.contactid) from tracking t2 where t2.click and t2.cid=tglob.cid) as countclicks from tracking tglob; ? > Mike > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Achilleas Mantzios
Michael Landin Hostbaek wrote: > A. Kretschmer (andreas.kretschmer) writes: >> *untested* >> >> select cid, sum(case when click = true then 1 else 0 end), sum(case when >> view = true then 1 else 0 end) from ... >> > > Thanks, but I need the DISTINCT contactid - I don't want the same > contactid counted twice. ... GROUP BY cid -- Richard Huxton Archonet Ltd
Michael Landin Hostbaek skrev: > Hello, > > I have a table called tracking, with a contactid varchar, click bool, > view bool and cid varchar. > > I would like to put the following into one single query if possible: > > // Number of clicks > select cid,count(distinct contactid) from tracking where click = > true group by cid; > > // Number of views > select cid,count(distinct contactid) from tracking where view = > true group by cid; Untested, not the cleverest formulation, but something like this should work: SELECT * FROM ( select cid,count(distinct contactid) from tracking where click = true group by cid ) c1 FULL OUTER JOIN ( select cid,count(distinct contactid) from tracking where view = true group by cid ) c2 USING (cid);
news.gmane.org (nis) writes: > SELECT * FROM > ( > select cid,count(distinct contactid) from tracking where click = > true group by cid > ) c1 > FULL OUTER JOIN > ( > select cid,count(distinct contactid) from tracking where view = > true group by cid > ) c2 > USING (cid); That did the trick! Many thanks, Mike