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!
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.
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
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
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
On 2016-03-24 Adam Thompson wrote:
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 =
Thanks Adam and John! I was so set on finding a join way I had forgotten about subqueries. Those ideas look like they will work fine. I once heard every subquery can be done as a join... how would you do the above with just joins (and without views)? Impossible? That's what I was trying to figure out and I just couldn't make it work. (Note, I wouldn't mind subqueries in the WHERE, to pull out sets, etc, but it would be nice to avoid the subqueries in the SELECT as they have to be run for every row the join/where selects, giving me, I think, O(n^2). Maybe my first hunch was correct and it's impossible to "flatten" this type of thing down to 1 simple-style query?
My SQL is very rusty by now, but I did lots of SQL decades ago. Although I've read all the responses, which are largely based on the concept of correlated queries, I'm going back to your original post. First, a question: Do you want two separate X and Y counts for each customer, or a combined "X or Y" count for each customer? If the latter, then I propose: SELECT custid, count(*) FROM ordertable WHERE (TTL=X or TTL=Y) GROUP BY custid ORDER BY custid; If the former, then I propose: SELECT custid, sum(totx), sum(toty) FROM (SELECT custid, count(*) as totx, 0 as toty FROM ordertable WHERE TTL=X GROUP BY custid UNION SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y GROUP BY custid) GROUP BY custid ORDER BY custid; If I remember correctly, unions producing a "full select" are automatically unioned with UNIQUE without specifying UNIQUE, but keep in mind that "uniqueness" is across ALL the fields in the SELECT, including aggregates like COUNT and SUM. Also, in some SQL's, WHERE's in subselects must be HAVING's. If the above nesting isn't allowed in your SQL, then you'd have to use a temporary table: CREATE TABLE custcountxy AS (SELECT custid, count(*) as totx, 0 as toty FROM ordertable WHERE TTL=X GROUP BY custid UNION SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y GROUP BY custid); SELECT custid, sum(totx), sum(toty) FROM custcountxy GROUP BY custid ORDER BY custid; DROP TABLE custcountxy; In some SQL's and in some of the above, ORDER BY custid would need to be ORDER BY 1 (1 referring to field #1). Hartmut W Sager - Tel +1-204-339-8331, +1-204-515-1701, +1-204-515-1700, +1-810-471-4600, +1-909-361-6005 On 24 March 2016 at 09:16, 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
On 2016-03-25 Hartmut W Sager wrote:
First, a question: Do you want two separate X and Y counts for each customer, or a combined "X or Y" count for each customer?
Option 1
If the former, then I propose:
SELECT custid, sum(totx), sum(toty) FROM (SELECT custid, count(*) as totx, 0 as toty FROM ordertable WHERE TTL=X GROUP BY custid UNION SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y GROUP BY custid) GROUP BY custid ORDER BY custid;
Ah! That's the trick! I was playing with unions and gave up because I was only trying: select a,b union select a,c and getting frustrated that the final result wasn't giving me a,b,c but was instead putting c values into a b labelled column! Your example by fudging 0 into the matching col name solves that! That may indeed be the solution, I'm changing my code now. I was reluctant to use subselects because (while they are working now) I am actually pulling out 6 counts, sums and avgs in 6 subselects on where's matching 10,000+ rows, meaning the db computation time is not optimal. Thanks!
Ah! That's the trick! I was playing with unions and gave up because I was only trying: select a,b union select a,c and getting frustrated that the final result wasn't giving me a,b,c but was instead putting c values into a b labelled column!
Yes, unions are weak that way - the matching isn't on column names, but rather on column positions. Hence also why some SQL's require ORDER BY 1 rather than ORDER BY custid.
Your example by fudging 0 into the matching col name solves that!
Yes, with the often-overlooked fact that a constant (like 0) is allowed in place of field name or expression. A constant is just a special case of expression. For further edification to everyone here: 1. Though implicit in the solutions I proposed, I should clarify that a GROUP BY clause always occurs together with aggregate functions in the SELECT, and is required when there are such aggregate functions. Otherwise, the result of SELECT wouldn't be a table equivalent by SQL definition, and it is an SQL requirement that the result of SELECT be a table equivalent. 2. I think I misdescribed HAVING. I believe HAVING comes right after a GROUP BY clause and is used to include/exclude the aggregate record resulting from the GROUP BY. Man, am I rusty on SQL! Hartmut W Sager - Tel +1-204-339-8331, +1-204-515-1701, +1-204-515-1700, +1-810-471-4600, +1-909-361-6005 On 25 March 2016 at 06:59, Trevor Cordes <trevor@tecnopolis.ca> wrote:
On 2016-03-25 Hartmut W Sager wrote:
First, a question: Do you want two separate X and Y counts for each customer, or a combined "X or Y" count for each customer?
Option 1
If the former, then I propose:
SELECT custid, sum(totx), sum(toty) FROM (SELECT custid, count(*) as totx, 0 as toty FROM ordertable WHERE TTL=X GROUP BY custid UNION SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y GROUP BY custid) GROUP BY custid ORDER BY custid;
Ah! That's the trick! I was playing with unions and gave up because I was only trying: select a,b union select a,c
and getting frustrated that the final result wasn't giving me a,b,c but was instead putting c values into a b labelled column!
Your example by fudging 0 into the matching col name solves that!
That may indeed be the solution, I'm changing my code now. I was reluctant to use subselects because (while they are working now) I am actually pulling out 6 counts, sums and avgs in 6 subselects on where's matching 10,000+ rows, meaning the db computation time is not optimal.
Thanks! _______________________________________________ Roundtable mailing list Roundtable@muug.mb.ca http://www.muug.mb.ca/mailman/listinfo/roundtable
participants (4)
-
Adam Thompson -
Hartmut W Sager -
John Lange -
Trevor Cordes