CODEWITHSUNDEEP
mysqlsql
Ned Hulton
Ned Hulton

Reputation: 678

Combine multiple SQL Queries for speed

I have a MySQL database with roughly 10 million rows and a simple SQL query:

SELECT output FROM `table` WHERE MA > Price

In addition to the above, I want to get:

The problem with my existing code (shown below) is that I am querying the table multiple times. This seems very inefficient.

Is it possible to query the database once and then extract information like average, standard deviation, etc. from the results? And if so, what would be the most efficient approach?

SELECT STDDEV_POP(output) FROM `table` WHERE MA > Price
SELECT AVG(output) FROM `table` WHERE MA > Price
SELECT COUNT(output) FROM `table` WHERE MA > Price AND output > 0
SELECT COUNT(output) FROM `table` WHERE MA > Price AND output < 0

***Edit Is it also possible to get the actual "output" values in addition to the mean, standard deviation, and percent positive? In other words, could the following query also be incorporated?

SELECT output FROM `table` WHERE MA > Price

Upvotes: 0

Views: 99

Answers (3)

Tin Tran
Tin Tran

Reputation: 6202

You could try grabbing them all with one query

   SELECT STDDEV_POP(output),AVG(output),
   SUM(output>0),
   SUM(output<0) FROM `table` WHERE MA > Price

Upvotes: 1

cableload
cableload

Reputation: 4375

You could do it in one query like this 

SELECT STDDEV_POP(output)
AVG(output),
count(case when output>0 then 1 else 0 end) as count1,
count(case when output<0 then 1 else 0 end) as count2,
FROM `table` WHERE MA > Price

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Yes. In this case, you just want some conditional aggregation:

SELECT STDDEV_POP(output) as stddev_pop,
       AVG(output) as avg,
       SUM(output > 0) as output_gt_0,
       SUM(output < 0) as output_lt_0
FROM `table`
WHERE MA > Price;

Upvotes: 5

Related Questions