Join multiple queries

Question

I have a big MySQL DDBB and some calcs and aggregations must be done. One of the tables, let say it called "thebigtable" got various info and some of the calcs retrieve different information.

Let's use an example for "thebigtable":

+-----+-----------+------------+---------+
| id  | secondid  | date       | value   |
+-----+-----------+------------+---------+
| 001 | AAA1      | 2010-01-01 | 1000.00 |
| 001 | AAA1      | 2010-02-01 | 1000.00 |
| 001 | AAA1      | 2010-03-01 | 1000.00 |
| 001 | AAA1      | 2010-04-01 | 1000.00 |
| 001 | AAA1      | 2010-05-01 | 1000.00 |
| 001 | AAA1      | 2010-06-01 | 1000.00 |
| 001 | AAA1      | 2010-07-01 | 1000.00 |
| 001 | AAA1      | 2010-08-01 | 1000.00 |
| 001 | AAA1      | 2010-09-01 | 1000.00 |
| 001 | AAA1      | 2010-10-01 | 1000.00 |
| 001 | AAA1      | 2010-11-01 | 1000.00 |
| 001 | AAA1      | 2010-12-01 | 1000.00 |
+-----+-----------+------------+---------+
| 001 | AAA2      | 2010-01-01 | 1000.00 |
| 001 | AAA2      | 2010-02-01 | 1000.00 |
| 001 | AAA2      | 2010-03-01 | 1000.00 |
| 001 | AAA2      | 2010-04-01 | 1000.00 |
| 001 | AAA2      | 2010-05-01 | 1000.00 |
| 001 | AAA2      | 2010-06-01 | 1000.00 |
| 001 | AAA2      | 2010-07-01 | 1000.00 |
| 001 | AAA2      | 2010-08-01 | 1000.00 |
| 001 | AAA2      | 2010-09-01 | 1000.00 |
| 001 | AAA2      | 2010-10-01 | 1000.00 |
| 001 | AAA2      | 2010-11-01 | 1000.00 |
| 001 | AAA2      | 2010-12-01 | 1000.00 |
+-----+-----------+------------+---------+
| 002 | AAA1      | 2010-01-01 | 1000.00 |
| 002 | AAA1      | 2010-02-01 | 1000.00 |
| 002 | AAA1      | 2010-03-01 | 1000.00 |
| 002 | AAA1      | 2010-04-01 | 1000.00 |
| 002 | AAA1      | 2010-05-01 | 1000.00 |
| 002 | AAA1      | 2010-06-01 | 1000.00 |
| 002 | AAA1      | 2010-07-01 | 1000.00 |
| 002 | AAA1      | 2010-08-01 | 1000.00 |
| 002 | AAA1      | 2010-09-01 | 1000.00 |
| 002 | AAA1      | 2010-10-01 | 1000.00 |
| 002 | AAA1      | 2010-11-01 | 1000.00 |
| 002 | AAA1      | 2010-12-01 | 1000.00 |
+-----+-----------+------------+---------+
| 002 | AAA2      | 2010-01-01 | 1000.00 |
| 002 | AAA2      | 2010-02-01 | 1000.00 |
| 002 | AAA2      | 2010-03-01 | 1000.00 |
| 002 | AAA2      | 2010-04-01 | 1000.00 |
| 002 | AAA2      | 2010-05-01 | 1000.00 |
| 002 | AAA2      | 2010-06-01 | 1000.00 |
| 002 | AAA2      | 2010-07-01 | 1000.00 |
| 002 | AAA2      | 2010-08-01 | 1000.00 |
| 002 | AAA2      | 2010-09-01 | 1000.00 |
| 002 | AAA2      | 2010-10-01 | 1000.00 |
| 002 | AAA2      | 2010-11-01 | 1000.00 |
| 002 | AAA2      | 2010-12-01 | 1000.00 |
+-----+-----------+------------+---------+

Ok. Let's now set the QUERIES I must perform. The first one, calculates the number of different 'secondid' for each 'id' with the condition that date is from month 08 to 10:

> SELECT id, COUNT(secondid) FROM thebigtable WHERE (date >= '2010-08-01') and (date <= '2010-10-01') GROUP BY id;

Result should be:

+-----+-----------------+
| id  | count(secondid) |
+-----+-----------------+
| 001 | 2               |
| 002 | 2               |
+-----+-----------------+

The second QUERY, should calculate the SUM of values for each id with the condition that date is just month 08:

> SELECT id, SUM(value) FROM thebigtable WHERE (date LIKE '2010-08-01') GROUP BY secondid;

Result should be:

+-----+-------------+
| id  | sum(values) |
+-----+-------------+
| 001 | 2000.00     |
| 002 | 2000.00     |
+-----+-------------+

I want to show both SELECT under just one result, ie:

+-----+-----------------+-------------+
| id  | count(secondid) | sum(values) |
+-----+-----------------+-------------+
| 001 | 2               | 2000.00     |
| 002 | 2               | 2000.00     |
+-----+-----------------+-------------+

I've tryed to create views and join then after, or to create an UNION of both SELECT, but none of them works.

Any help?

Answer 1

I hope this will help you:

SELECT id, SUM(cnt) cnt, SUM(IF(yemo='1008',val,0)) val FROM (
 SELECT id,
        date_format(`date`,'%y%m')  yemo, 
        COUNT(secondid)             cnt,
        SUM(`value`)                val
 FROM bigtable GROUP BY id,date_format(`date`,'%y%m')
) t WHERE yemo between '1008' AND '1009' GROUP BY id

Although, according to my SQLfiddle the resulting number for column count(secondid) in your first query should be 4 and not 2 for the two ids in question. Or have I missed a point ?!?

Answer 2

Try using Subselects, like

SELECT a.id, b.secondid, c.value_sum 
FROM
    thebigtable a,
    ( SELECT id, COUNT( secondid ) as sec 
      FROM thebigtable ) b,
    ( SELECT id, SUM( value ) as value_sum 
      FROM thebigtable 
      GROUP BY secondid ) c,
WHERE a.id = b.id
AND   a.id = c.id