MySQL多个子查询，获取日期间隔中每天的特定列的第一个值
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;
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