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;
