[RndTbl] SQL problem

Adam Thompson athompso at athompso.net
Thu Mar 24 13:06:14 CDT 2016


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 at johnlange.ca 
> <mailto:john at 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 at athompso.net <mailto:athompso at 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 at tecnopolis.ca <mailto:trevor at 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 at muug.mb.ca
>             <mailto:Roundtable at 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 at muug.mb.ca
>         <mailto:Roundtable at 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 at muug.mb.ca
> http://www.muug.mb.ca/mailman/listinfo/roundtable
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.muug.mb.ca/pipermail/roundtable/attachments/20160324/ecf3d6af/attachment.html>


More information about the Roundtable mailing list