MySQL多个子查询，获取日期间隔中每天的特定列的第一个值

Question

I have been stuck on the following query for some time and can't figure out how to do it (explanation below):

SELECT 
a.id, a.name, t.agency_id, t.initial_amount,
DATE_FORMAT(t.created_at, "%d.%m.%Y") as date,
DATE_FORMAT(t.created_at, "%Y-%m-%d") as date_created,
t.created_at, t.collection, t.pay, (t.initial_amount + t.collection - t.pay) as total_amount
FROM (
 SELECT
    hh.agency_id, hh.amount, h0.initial_amount, hh.created_at,
    SUM(IF(hh.payment_type=0,hh.amount,0)) AS collection,
    SUM(IF(hh.payment_type=1,hh.amount,0)) AS pay
 FROM house_register AS hh
 LEFT JOIN (
    SELECT h1.initial_amount, h1.agency_id, h1.created_at 
    FROM house_register AS h1
    INNER JOIN (
        SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
        FROM house_register AS h2 
        GROUP BY h2.agency_id
    ) AS min_id_ag
        ON h1.agency_id = min_id_ag.agency_id
        AND h1.id = min_id_ag.min_id
) AS h0 
    ON hh.agency_id = h0.agency_id
WHERE hh.created_at BETWEEN '2015-11-01' AND '2015-11-30' 
GROUP BY hh.created_at, hh.agency_id WITH ROLLUP
) AS t 
INNER JOIN agencies as a
on a.id = t.agency_id

So, what I'm trying to achieve here is getting a report from a certain period of time from a single table, in this table I have operations that have a type status(payment or collection) and each operation corresponds to a agency_id (store).

My report has to be something like this:

Columns:

agency_id    date X  initial_amount_of_first_transaction_of_day_in_loop sum(payments) sum(collection)

Rows will look something like this:

agency_1 2015-01-01 500 100 0
agency_2 2015-01-01 600 100 0
.... next date
agency_1 2015-01-02 600 0 150
agency_2 2015-01-02 450 0 150
etc

What I want is to get the initial_amount of the first transaction (hence the min(id)) for each date grouped by agency_id and created_at. For example if I have 50 transactions on the date 2015-01-01, I want to get the value of the column initial_amount of the first transaction in that date.

Now it just selects the minimum value of the whole date interval. And I the same minimum value for all the dates.

Hope I was clear enough.

Thank you for the help!

Update Structure:

CREATE TABLE IF NOT EXISTS `house_register` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` date DEFAULT NULL,
`created_time` time DEFAULT NULL,
`initial_amount` decimal(14,2) DEFAULT NULL,
`document_type` tinyint(3) unsigned DEFAULT NULL,
`document_id` int(10) unsigned DEFAULT NULL,
`document_number` varchar(100) DEFAULT NULL,
`description` varchar(200) DEFAULT NULL,
`amount` decimal(14,2) DEFAULT NULL,
`final_amount` decimal(14,2) DEFAULT NULL,
`agency_id` int(11) DEFAULT NULL,
`user_id` int(10) DEFAULT NULL,
`payment_type` tinyint(1) DEFAULT NULL COMMENT '0 - incasare, 1 - plata',
`number` varchar(50) DEFAULT NULL,
`debit_account` varchar(45) DEFAULT NULL,
`credit_account` varchar(45) DEFAULT NULL,
`short_desc` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34;

Update query breakdown with result set:

The complete one returns this: Results complete sql

Subquery 1: Subquery 1 results

SELECT
hh.agency_id, hh.amount, h0.initial_amount, hh.created_at,
SUM(IF(hh.payment_type=0,hh.amount,0)) AS collection,
SUM(IF(hh.payment_type=1,hh.amount,0)) AS pay
FROM house_register AS hh
LEFT JOIN (
SELECT h1.initial_amount, h1.agency_id, h1.created_at 
FROM house_register AS h1
INNER JOIN (
    SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
    FROM house_register AS h2 
    GROUP BY h2.agency_id
) AS min_id_ag
    ON h1.agency_id = min_id_ag.agency_id
    AND h1.id = min_id_ag.min_id
) AS h0 
ON hh.agency_id = h0.agency_id
WHERE hh.created_at BETWEEN '2015-11-01' AND '2015-11-30' 
GROUP BY hh.created_at, hh.agency_id WITH ROLLUP;

Subquery 2:See comment

SELECT h1.initial_amount, h1.agency_id, h1.created_at 
FROM house_register AS h1
INNER JOIN (
    SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
    FROM house_register AS h2 
    GROUP BY h2.agency_id
) AS min_id_ag
    ON h1.agency_id = min_id_ag.agency_id
    AND h1.id = min_id_ag.min_id;

Subquery 3:See comment

SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
    FROM house_register AS h2 
    GROUP BY h2.agency_id;

Answer 1

Sorry, yes, I had to re-read your question a couple of times, but I think I've got it sorted. Does this give you the results you require?

For the min(h2.id) select you need to group by h2.created_at also, not just the h2.agency_id - full group by = GROUP BY h2.agency_id, h2.created_at at line 18.

SELECT 
a.id, a.name, t.agency_id, t.initial_amount,
DATE_FORMAT(t.created_at, "%d.%m.%Y") as date,
DATE_FORMAT(t.created_at, "%Y-%m-%d") as date_created,
t.created_at, t.collection, t.pay, (t.initial_amount + t.collection - t.pay) as total_amount
FROM (
 SELECT
    hh.agency_id, hh.amount, h0.initial_amount, hh.created_at,
    SUM(IF(hh.payment_type=0,hh.amount,0)) AS collection,
    SUM(IF(hh.payment_type=1,hh.amount,0)) AS pay
 FROM house_register AS hh
 LEFT JOIN (
    SELECT h1.initial_amount, h1.agency_id, h1.created_at 
    FROM house_register AS h1
    INNER JOIN (
        SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
        FROM house_register AS h2 
        GROUP BY h2.agency_id, h2.created_at
    ) AS min_id_ag
        ON h1.agency_id = min_id_ag.agency_id
        AND h1.id = min_id_ag.min_id
) AS h0 
    ON hh.agency_id = h0.agency_id
WHERE hh.created_at BETWEEN '2015-11-01' AND '2015-11-30' 
GROUP BY hh.created_at, hh.agency_id WITH ROLLUP
) AS t 
INNER JOIN agencies as a
on a.id = t.agency_id