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 and TTL=Y)
FROM ordertable
order by custid;

On Thu, Mar 24, 2016 at 11:50 AM, John Lange <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> 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> 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 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 http://www.muug.mb.ca/mailman/listinfo/roundtable
--
John Lange www.johnlange.ca
--
John Lange www.johnlange.ca
_______________________________________________
Roundtable mailing list
Roundtable@muug.mb.ca
http://www.muug.mb.ca/mailman/listinfo/roundtable