MySQL Multiple subqueries, get first value of a specific column for each day in a date interval

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;

Answers


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

I believe you would need to add the date (created_at) to the group by as days, and the where clause for between dates.

        WHERE h2.created_at BETWEEN '2015-11-01' AND '2015-11-30' 
        GROUP BY h2.agency_id, date_format(h2.created_at, "%Y-%b-%d")

First line cuts down the search criteria to just the dates you want. The second line groups everything by agency_id, and then the day (by grouping on created_at formatted YYY-MM-DD.

i.e. something like (I haven't tested but...)

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 
        WHERE h2.created_at BETWEEN '2015-11-01' AND '2015-11-30' 
        GROUP BY h2.agency_id, date_format(h2.created_at, "%Y-%b-%d")
    ) 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

You answer was almost correct, definitely got me on the right track to fixing it. Thank you very much for you help and pacience @wkdmarty!

This is the query I wanted.

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 
                                      WHERE      h1.created_at BETWEEN '2015-11-01' AND '2015-11-30' ) AS h0 
                 ON        hh.agency_id = h0.agency_id 
                 AND       hh.created_at = h0.created_at 
                 WHERE     h0.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 
ORDER BY t.agency_id, 
         t.created_at

Need Your Help

WPF Binding to Radio Buttons If Radio Buttons duplicated to same property .,.,. not updating the Model

c# wpf mvvm

Here This is the View which the Radio buttons had been bound to a Enum Property using IValueConverter(Works Fine)