[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