<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
IIRC, on SQL-92-compliant databases (which MySQL 5.5 is *not*, but
this syntax might work anyway), the correct syntax will be:<br>
<br>
SELECT a.firstcolumn ,<br>
(SELECT count(*) FROM firsttable AS b WHERE b.firstcolumn =
a.firstcolumn AND b.secondcolumn = 'VALUE1' ) ,<br>
(SELECT count(*) FROM firsttable AS c WHERE c.firstcolumn =
a.firstcolumn AND c.secondcolumn = 'VALUE2' )<br>
FROM firsttable AS a<br>
ORDER BY a.firstcolumn;<br>
<br>
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*)<br>
<br>
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:<br>
<br>
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';<br>
<br>
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';<br>
<br>
3. SELECT a.firstcolumn, b.countvalue, c.countvalue FROM firsttable
NATURAL JOIN x_temp_view_1 NATURAL JOIN x_temp_view_2;<br>
<br>
-Adam<br>
<br>
<br>
<br>
<br>
<div class="moz-cite-prefix">On 16-03-24 11:55 AM, John Lange wrote:<br>
</div>
<blockquote
cite="mid:CAPfXoshs-zqG438HQe9YGk22DBXh+_N6Tniu6PQiXr_m8gghPQ@mail.gmail.com"
type="cite">
<div dir="ltr">Sorry, I see that was totally wrong... You'd need
something like below but I don't think this will work as-is...
<div><br>
</div>
<div>
<div style="font-size:12.8px"><span style="font-size:12.8px">SELECT
custid as cid, (select count(*) FROM ordertable where
custid=cid and TTL=X), (select count(*) FROM ordertable
where </span><span style="font-size:12.8px">custid=cid and</span><span
style="font-size:12.8px"> </span><span
style="font-size:12.8px">TTL=Y)</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px">FROM
ordertable</span></div>
<div style="font-size:12.8px">order by custid;</div>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Thu, Mar 24, 2016 at 11:50 AM, John
Lange <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:john@johnlange.ca" target="_blank">john@johnlange.ca</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">What Adam said; something like:
<div><br>
</div>
<div><span style="font-size:12.8px">SELECT custid, (select
count(*) FROM ordertable where TTL=X), (select
count(*) FROM ordertable where TTL=Y)</span><br
style="font-size:12.8px">
<span style="font-size:12.8px">FROM ordertable</span></div>
<div>order by custid;</div>
<div><br>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">
<div>
<div class="h5">On Thu, Mar 24, 2016 at 9:21 AM, Adam
Thompson <span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:athompso@athompso.net"
target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:athompso@athompso.net">athompso@athompso.net</a></a>></span>
wrote:<br>
</div>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<div class="h5">
<div>Subqueries:<br>
SELECT (SELECT ...), (SELECT ...) FROM ...<br>
<br>
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).<br>
<br>
Alternately, create two VIEWs, one for each
aggregate count, and OUTER JOIN them.<br>
-Adam<br>
<br>
<br>
<div class="gmail_quote"><span>On March 24, 2016
9:16:31 AM CDT, Trevor Cordes <<a
moz-do-not-send="true"
href="mailto:trevor@tecnopolis.ca"
target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:trevor@tecnopolis.ca">trevor@tecnopolis.ca</a></a>>
wrote:</span>
<blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt
0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">
<pre><span>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!
<hr>
</span><span>Roundtable mailing list
<a moz-do-not-send="true" href="mailto:Roundtable@muug.mb.ca" target="_blank">Roundtable@muug.mb.ca</a>
<a moz-do-not-send="true" href="http://www.muug.mb.ca/mailman/listinfo/roundtable" target="_blank">http://www.muug.mb.ca/mailman/listinfo/roundtable</a>
</span></pre></blockquote></div><span><font color="#888888">
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.</font></span></div>
</div></div>_______________________________________________<span class="">
Roundtable mailing list
<a moz-do-not-send="true" href="mailto:Roundtable@muug.mb.ca" target="_blank">Roundtable@muug.mb.ca</a>
<a moz-do-not-send="true" href="http://www.muug.mb.ca/mailman/listinfo/roundtable" rel="noreferrer" target="_blank">http://www.muug.mb.ca/mailman/listinfo/roundtable</a>
</span></blockquote></div><span class="HOEnZb"><font color="#888888">
<div>
</div>--
<div>John Lange
<a moz-do-not-send="true" href="http://www.johnlange.ca" target="_blank">www.johnlange.ca</a></div>
</font></span></div>
</blockquote></div>
<div>
</div>--
<div class="gmail_signature">John Lange
<a moz-do-not-send="true" href="http://www.johnlange.ca" target="_blank">www.johnlange.ca</a></div>
</div>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre wrap="">_______________________________________________
Roundtable mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Roundtable@muug.mb.ca">Roundtable@muug.mb.ca</a>
<a class="moz-txt-link-freetext" href="http://www.muug.mb.ca/mailman/listinfo/roundtable">http://www.muug.mb.ca/mailman/listinfo/roundtable</a>
</pre>
</blockquote>
</body></html>