StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJoin multiple queries
text
Body
copied!<p>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.</p> <p>Let's use an example for "thebigtable":</p> <pre><code>+-----+-----------+------------+---------+ | 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 | +-----+-----------+------------+---------+ </code></pre> <p>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:</p> <pre><code>> SELECT id, COUNT(secondid) FROM thebigtable WHERE (date >= '2010-08-01') and (date <= '2010-10-01') GROUP BY id; </code></pre> <p>Result should be:</p> <pre><code>+-----+-----------------+ | id | count(secondid) | +-----+-----------------+ | 001 | 2 | | 002 | 2 | +-----+-----------------+ </code></pre> <p>The second QUERY, should calculate the SUM of values for each id with the condition that date is just month 08:</p> <pre><code>> SELECT id, SUM(value) FROM thebigtable WHERE (date LIKE '2010-08-01') GROUP BY secondid; </code></pre> <p>Result should be:</p> <pre><code>+-----+-------------+ | id | sum(values) | +-----+-------------+ | 001 | 2000.00 | | 002 | 2000.00 | +-----+-------------+ </code></pre> <p>I want to show both SELECT under just one result, ie:</p> <pre><code>+-----+-----------------+-------------+ | id | count(secondid) | sum(values) | +-----+-----------------+-------------+ | 001 | 2 | 2000.00 | | 002 | 2 | 2000.00 | +-----+-----------------+-------------+ </code></pre> <p>I've tryed to create views and join then after, or to create an UNION of both SELECT, but none of them works.</p> <p>Any help?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload