IIRC, on SQL-92-compliant databases (which MySQL 5.5 is *not*, but this syntax might work anyway), the correct syntax will be:
SELECT a.firstcolumn , (SELECT count(*) FROM firsttable AS b WHERE b.firstcolumn = a.firstcolumn AND b.secondcolumn = 'VALUE1' ) , (SELECT count(*) FROM firsttable AS c WHERE c.firstcolumn = a.firstcolumn AND c.secondcolumn = 'VALUE2' ) FROM firsttable AS a ORDER BY a.firstcolumn;
Some databases don't do subqueries at all. Some only do them in WHERE clauses. Some will only do a single subquery per query. Some can do arbitrarily-nested subqueries. Some can only do one level of subquery. YMMV, even within a single product - MySQL 5.5. can do a lot more than 5.1 could, for example. (And PostgreSQL can pretty much do it all... only 15+ years after Watcom SQL managed to be almost fully SQL-92 compliant before Sybase murdered it. *grumble*)
If you can't do subqueries, your user will need CREATE/DROP permissions in some database / tablespace / schema, and you'll have to do something like:
1. CREATE OR REPLACE VIEW x_temp_view_1 AS SELECT a.firstcolumn AS firstcolumn, count(*) AS countvalue FROM firsttable AS a WHERE a.secondcolumn='VALUE1';
2. CREATE OR REPLACE VIEW x_temp_view_2 AS SELECT a.firstcolumn AS firstcolumn, count(*) AS countvalue FROM firsttable AS a WHERE a.secondcolumn='VALUE2';
3. SELECT a.firstcolumn, b.countvalue, c.countvalue FROM firsttable NATURAL JOIN x_temp_view_1 NATURAL JOIN x_temp_view_2;
-Adam
On 16-03-24 11:55 AM, John Lange wrote:
Sorry, I see that was totally wrong... You'd need something like below but I don't think this will work as-is...
SELECT custid as cid, (select count(*) FROM ordertable where custid=cid and TTL=X), (select count(*) FROM ordertable where custid=cid andTTL=Y) FROM ordertable order by custid;
On Thu, Mar 24, 2016 at 11:50 AM, John Lange <john@johnlange.ca mailto:john@johnlange.ca> wrote:
What Adam said; something like: SELECT custid, (select count(*) FROM ordertable where TTL=X), (select count(*) FROM ordertable where TTL=Y) FROM ordertable order by custid; On Thu, Mar 24, 2016 at 9:21 AM, Adam Thompson <athompso@athompso.net <mailto:athompso@athompso.net>> wrote: Subqueries: SELECT (SELECT ...), (SELECT ...) FROM ... If they don't work, upgrade to a newer version of MariaDB. (If this is the customer I assume it is, use RackSpace's IUS repo to upgrade). Alternately, create two VIEWs, one for each aggregate count, and OUTER JOIN them. -Adam On March 24, 2016 9:16:31 AM CDT, Trevor Cordes <trevor@tecnopolis.ca <mailto:trevor@tecnopolis.ca>> wrote: Can anyone help me figure out how to do this in SQL (MySQL)? (pseudo-code giving you the gist): SELECT custid, (count orders where ttl=X), (count orders where ttl=Y) FROM ordertable GROUP BY custid; I basically want to count two different things based on two different wheres. If I put in a where clause then I select either the X or the Y but I can't seem to get both in such a way I can count the X's and Y's. I tried thinking about unions but couldn't make it work. I want to do this all in 1 query as I want the sorted union of all custid's (X and Y). P.S. ordertable has/can have multiple entries for each custid. Ideas are appreciated! ------------------------------------------------------------------------ Roundtable mailing list Roundtable@muug.mb.ca <mailto:Roundtable@muug.mb.ca> http://www.muug.mb.ca/mailman/listinfo/roundtable -- Sent from my Android device with K-9 Mail. Please excuse my brevity. _______________________________________________Roundtable mailing list Roundtable@muug.mb.ca <mailto:Roundtable@muug.mb.ca> http://www.muug.mb.ca/mailman/listinfo/roundtable -- John Lange www.johnlange.ca <http://www.johnlange.ca>-- John Lange www.johnlange.ca http://www.johnlange.ca
Roundtable mailing list Roundtable@muug.mb.ca http://www.muug.mb.ca/mailman/listinfo/roundtable