How to use Subquery Result in SELECT calculation

I just figured out how to use Subqueries in my SELECT to calculate a value.

Now I would like to take two such values - referenced by aliases - and calculate a third value in the SELECT.

Here is a query snippet…


SELECT m.id AS member_id, m.username, m.location,

	(SELECT COUNT(id)
	FROM article_comment_review
	WHERE...) AS reviewCount,

	(SELECT COUNT(id)
	FROM article_comment_review
	WHERE...) AS agreeCount,

	(agreeCount/reviewCount) * 100 AS pctAgree


How can I make this work?

Sincerely,

Debbie

SELECT member_id
     , username
     , location
     , reviewCount
     , agreeCount
     , 100.0 * agreeCount / reviewCount AS pctAgree
  FROM ( SELECT m.id AS member_id
              , m.username
              , m.location
              , ( SELECT COUNT(id)
                   FROM article_comment_review
                  WHERE... ) AS reviewCount
             , ( SELECT COUNT(id)
                   FROM article_comment_review
                  WHERE... ) AS agreeCount
           FROM doobie AS m
          WHERE ...
       ) AS s

doobie?

Been smokin again?

Isn’t there a way to use the results from calculated fields directly versus wrapping everything in a parent SELECT?

Sincerely,

Debbie

you could declare a VIEW

So which way is better?

And what do YOU do when you need to add statistics to records like I am doing?

Debbie

I think that a view would be easier maintain, in case you need to change anything down the road, or if you are simply reading it and trying to figure out what’s going on, but my guess is that the query he put up in his first response will execute a little faster. So it’s up to you to decide which is more important…easier but slower or more complex but faster

But if my assumption that the view will be slower is incorrect, this response was pointless :smiley:

another approach is to run just the subquery by itself, and do the pctAgree calculation in the application layer (php or whatever)

My current query adds on 4 new subqueries weighing in at 56 lines of SQL… (A new record for Debbie!) :smiley:

I am pretty sure that it is more efficient to run ONE query and get all of the data you need in one trip, versus running several smaller queries, and thus several trips.

However, in a case like this where subqueries are involved, maybe that is not the case?!

When I ran this new monster query with some test data, this is what phpMyAdmin shows…


Showing rows 0 - 99 (120 total, Query took 0.0131 sec)

(Not sure how that would translate to having 500 concurrent users all running this query at the same time?!) :-/

Sincerely,

Debbie

in general, this is true

perhaps you misunderstood what i meant when i said “run the subquery by itself”

i was referring to subquery “s” in post #2 – in response to your question “Isn’t there a way to use the results from calculated fields directly versus wrapping everything in a parent SELECT?”

i wasn’t suggesting running a whole series of little queries

the query in subquery “s” ~does~ get all the data you need… and then just calculate the pctAgree in php

Oh, right, I follow you now!

BTW, any thoughts on my monster query containing 5 subqueries and “performance bottlenecks” as my website’s traffic grows?

Again, this query returns Comments beneath an Article, as well as “reviews” for said Comments.

I estimate there will be maybe 100-200 Comments/Article.

As far as Reviews, well, I would hope that would go into the thousands as more and more people visit my site.

I guess these results seem okay to me…


Showing rows 0 - 99 (120 total, Query took 0.0131 sec)

…but who knows?!

Sincerely,

Debbie

with the proper indexes, you’ll be fine

I’m sorry, but I don’t know much about Indexes or where to put them on a complicated query like this. :blush:

(Up until this point, the only time I have created indexes was either when I needed a Unique Index, or when dealing with phpMyAdmin’s desire for Indexes on Foreign Keys.)

“Database Tuning” is a whole topic on which I have basically no knowledge…

Sincerely,

Debbie

indexes go on tables, not queries

0.013 seconds means you don’t have to worry for a long time, if at all

Right, but I meant that it is not clear to me which columns I should place indexes on in my tables so my queries run more efficiently…

Up to this point, I have just created Indexes to give me “uniqueness” and to make it so I can do joins using Foreign Keys.

When I ran EXPLAIN I see this… (see screenshot)

When I look at the first two rows in EXPLAIN, they sort of concern me, because it looks like they are not using Indexes and instead are doing “Full Table Scans”…


id	select_type	table		type	possible_keys	key	key_len	ref	rows	Extra 	
1	PRIMARY		<derived2>	ALL	NULL		NULL	NULL	NULL	120	Using filesort
2	DERIVED		m		ALL	PRIMARY		NULL	NULL	NULL	14 	

Can you help me understand what EXPLAIN is actually saying??

Sincerely,

Debbie