Subtracting two Counts

[gnznroses]

What I would like to do is the equivalent of this:

note the line that doesn't work is (num1-num2) AS conv

 

SELECT c.id, c.name,
                ( SELECT COUNT(*) FROM table2 WHERE table2.cid=c.id) AS num1,
                ( SELECT COUNT(*) FROM table3 WHERE table3.cid=c.id) AS num2,
                (num1-num2) AS conv
              FROM cookies c, lollipops l
              WHERE l.cid = c.id
                AND l.blah = 1
              GROUP BY l.cid
              ORDER BY conv DESC
              LIMIT 10

 

 

i changed table names and simplified it for privacy reasons

it seems you can't subtract two column aliases (the two Counts)?

 

 

so i tried something more complex, using derived tables, and then trying the subtraction again like that, but then in my derived table, it says c.id is an unknown column:

 

SELECT CC.num1 AS mytotal1, CD.num2 AS mytotal2, c.id, c.name, (mytotal1-mytotal2) AS difference
FROM
(SELECT COUNT(*) AS num1 FROM table2 WHERE table2.cid=c.id) AS CC,
(SELECT COUNT(*) AS num2 FROM table3 WHERE table3.cid=c.id) AS CD,
cookies c, lollipops l

...

 

 

i wouldn't expect this to be so hard... i just need to be able to ORDER BY the difference between two counts.

 

 

 

 

on a related note, if i select two SUMs and give them aliases, and then subtract those two:

SELECT SUM(l.num) AS num1, SUM(c.num) AS num2, (SUM(l.num)-SUM(c.num)) AS diff

will it calculate the sums two times each, or will it optimize it? again i can't just subtract them based on the assigned aliases, but it does work the way it is in that code.

[gnznroses]

hmm, maybe the only way to do this is to create a temporary table, wiht a column that holds the difference in numbers. and then do a new Select and sort by that column?

[fenway]

You can't use column aliases *during* a query...

 

SELECT CC.num1 AS mytotal1, CD.num2 AS mytotal2, c.id, c.name, (mytotal1-mytotal2) AS difference

 

needs to be

 

SELECT CC.num1 AS mytotal1, CD.num2 AS mytotal2, c.id, c.name, (CC.num1-CD.num2) AS difference

[gnznroses]

Thanks, but it still says c.id is an unknown column in my Where clause.

Pretty sure it means the use of it in my derived table.

Changing it from c to cookies doesn't help.

 

I assume if I changed it to:

(SELECT COUNT(*) AS num1 FROM table2, cookies AS c WHERE table2.cid=c.id) AS CC,

 

that it would no longer be related to the main query (the other table i'm joining this with)?

[fenway]

Did you try moving it "up" the join list? Your table order isn't explicit....

[gnznroses]

well, in my code, i actually do have it arranged differently than stated earlier.

like this:

SELECT .....
FROM
cookies c, lollipops l,
(SELECT COUNT(*) AS num1 FROM table2 WHERE table2.cid=c.id) AS CC,
(SELECT COUNT(*) AS num2 FROM table3 WHERE table3.cid=c.id) AS CD

[fenway]

How come we're talking about code that you haven't "stated"?

[gnznroses]

It's the same code, just with the "From" statements in a different order (which is what I assume you suggested in your last reply).

 

here though is my current and admittedly a bit different code. (the goal is to get a list of the "cookies" with the lowest "lollies-to-clowns" ratio.)

 

SELECT c.id, c.name, SUM(l.col) AS sum1,
  LL.numlollis_t AS numlollis, CC.numclowns_t AS numclowns,
  (LL.numlollis_t/CC.numclowns_t) AS perc
FROM cookies c, lollipops l,
    (SELECT COUNT(*) AS numlollis_t FROM lollipops ls WHERE ls.cid=cookies.id AND ls.status IN (1,2,5,6)) AS LL,
    (SELECT COUNT(*) AS numclowns_t FROM clowns cl WHERE cl.cid=cookies.id) AS CC
WHERE l.cid = c.id
  AND l.status IN (1,2,5,6)
GROUP BY l.cid
ORDER BY perc ASC
LIMIT 10

 

produces the error "unknown column cookies.id"

 

if i change

(SELECT COUNT(*) AS numlollis_t FROM lollipops ls WHERE ls.cid=cookies.id AND ls.status IN (1,2,5,6)) AS LL,

to

(SELECT COUNT(*) AS numlollis_t FROM lollipops ls, cookies WHERE ls.cid=cookies.id AND ls.status IN (1,2,5,6)) AS LL,

it works but it's no longer joining with the rest of my query. it returns the same count for each row of the final result.

[fenway]

well, you aliased the table,and then you're not using the alias....

[gnznroses]

I know you're tryign to help and I appreciate it, but you're not following along very well

 

Thanks, but it still says c.id is an unknown column in my Where clause.

Pretty sure it means the use of it in my derived table.

Changing it from c to cookies doesn't help.

 

So yes, I've tried using the alias.

[fenway]

Well, I can't do anything else without your table structures, since I can't execute the query on my end -- nor have you provided your version number.

[gnznroses]

sent you a PM

[fenway]

sent you a PM

Yes, I saw... IMHO, table structures don't really constitute something to hide.  I'll make a one-time exception this time.  Keep discussions to the boards for future reference.

 

Why not simply sort by "numleads/numclicks" instead of "convratio"? Problem solved.

[gnznroses]

ah, because i need to see what the convratio actually is, for each result.

because what we need is to not only sort them, but also see how high or low the top/bottom results are, and whether some results are a lot higher than the next highest result.

 

-edit-

oh wait, i guess that does solve the problem. i can simply return the convratio as one of the columns, and then sort them the way you said instead of by that column.

 

it's really odd that mysql is doing this though. there's really not a difference in what is happening, just the way you express it.

[gnznroses]

oh, on a sidenote, do you know if that division is done twice on every row? meaning i return one column that is col1/col2 and then i sort by col1/col2, so does mysql have to do the math on every row twice or does it optimize it?

 

probably doesn't matter, but if anyone knows...

[gnznroses]

sorry for the multiple replies but i thought i'dpoint out how retarded mysql is being

 

i can ORDER BY numleads/numclicks

but i still want to know what the ratio is, so i add this to my Select statement:

(numleads/numclicks) AS convratio

and it tells me that numleads is an unknown column.

that makes NO sense...

 

i can do the math in php after i get the results, to display it, but c'mon, that's just dumb.

[fenway]

sorry for the multiple replies but i thought i'dpoint out how retarded mysql is being

 

i can ORDER BY numleads/numclicks

but i still want to know what the ratio is, so i add this to my Select statement:

(numleads/numclicks) AS convratio

and it tells me that numleads is an unknown column.

that makes NO sense...

 

i can do the math in php after i get the results, to display it, but c'mon, that's just dumb.

Now wait just a second -- it's not "retarded" at all.

 

MySQL doesn't even look at the column list until the final steps of statement processing -- which is why you can't use column aliases in the WHERE clause.  Since the aliasing is performed in a single-pass, there's no possible way to get the alias of an alias -- which is exactly what you're asking for with "convratio".  Think about it for a minute, and it should make sense why you can't do it.

 

As for your question about the same expression appearing twice, the optimizer should pick that up -- unless it's in a dependent subquery, and then all bets are off.

 

Furthermore, there is absolutely no reason why you can't just get the ratio in php and add it to the column list.  The other option is simply to actually derive convratio as the ratio of two subqueries in the column list... but I can't imagine that would be faster...

 

Hope this clarifies things a bi.

[gnznroses]

Ah, OK thanks. I understand. I really appreciate your help

[Phoenix~Fire]

use mysql's SUM() function.  I'm pretty sure you cant' use num1 and num2 inside of the statement.

 

SUM(count(table1.*),count(table2.*));