SQL value - MAX does not match the corresponding values
I need to extract data for each user based on the latest account data. Here's the data:
CREATE TABLE bite
(`ACCOUNT` int, `ISSUEDATE` datetime, `REFERENCE` int,
`ID` int, `AMOUNT` decimal(10,2))
;
INSERT INTO bite
(`ACCOUNT`, `ISSUEDATE`, `REFERENCE`, `ID`, `AMOUNT`)
VALUES
(2947471, '2012-12-31 00:00:00', 0005632765, 11543487, 40.18),
(2947471, '2016-12-30 00:00:00', 0017945914, 36672073, 34.75),
(2947471, '2012-11-30 00:00:00', 0005448824, 11001690, 33.69),
(2947471, '2013-03-31 00:00:00', 0006316596, 12759258, 33.44),
(2956987, '2015-07-01 00:00:00', 0012754607, 26035486, 26.79),
(2956987, '2015-12-30 00:00:00', 0014371958, 29254616, 25.14),
(2947471, '2014-05-31 00:00:00', 0009478470, 19223669, 23.34),
(2947471, '2013-03-02 00:00:00', 0006177725, 12352924, 21.17),
(2956987, '2014-05-31 00:00:00', 0009504785, 19223632, 20.24),
(2947471, '2014-01-31 00:00:00', 0008753563, 17845265, 19.97),
(2947471, '2013-06-30 00:00:00', 0007174573, 14523560, 19.28),
(2947471, '2014-06-30 00:00:00', 0010048671, 20171164, 19.22),
(2947471, '2015-01-31 00:00:00', 0011793537, 23926223, 18.98),
(2947471, '2014-03-31 00:00:00', 0009215609, 18762696, 18.26),
(2947471, '2013-11-30 00:00:00', 0008394094, 16600223, 18.14),
(2947471, '2013-04-30 00:00:00', 0006818798, 13568161, 18.08),
(2956987, '2015-06-30 00:00:00', 0013061086, 26579781, 18.03),
(2956987, '2014-10-31 00:00:00', 0010942060, 22376155, 18),
(2947471, '2013-12-31 00:00:00', 0008440468, 17395187, 18),
(2947471, '2013-10-31 00:00:00', 0007958174, 16156026, 17.94),
(2947471, '2013-08-31 00:00:00', 0007585482, 15274737, 17.79),
(2947471, '2012-11-30 00:00:00', 0005632765, 11543487, 40.18),
(2947471, '2016-11-30 00:00:00', 0017945914, 36672073, 34.75),
(2947471, '2012-10-31 00:00:00', 0005448824, 11001690, 33.69),
(2947471, '2013-02-28 00:00:00', 0006316596, 12759258, 33.44),
(2956987, '2015-05-31 00:00:00', 0012754607, 26035486, 26.79),
(2956987, '2015-11-30 00:00:00', 0014371958, 29254616, 25.14)
;
And this is the request:
select account, max(issuedate) as "LAST INVOICE DATE",
reference,amount,round(avg(amount),2) as "AVERAGE AMOUNT"
from bite
group by account
(can also be found here: http://sqlfiddle.com/#!9/5280b7/58/0 )
You will see that I have everything about everything, so to speak, but not the maximum date or vice versa. I want each record from the columns related to the maximum date value. Please tell me what I am doing wrong.
+3
source to share
2 answers
To get just the data from the latter issuedate
, you would need to use a subquery
to get max issuedate
:
select account,
max(issuedate) as "LAST INVOICE DATE",
amount,
(select round(avg(b3.amount),2)
from bite b3
where b3.account = b.account) as "AVERAGE AMOUNT"
from bite b
where b.issuedate = (select max(b2.issuedate)
from bite b2
where b2.account = b.account)
group by account, issuedate
0
source to share
I hope the code helps you.
with withbite
as(
select row_number()over(partition by account order by issuedate desc) as line,
account,
issuedate,
reference,
amount
from bite
)
select account,
issuedate as 'LAST INVOICE DATE',
reference,
amount,
round(avg(amount),2) as 'AVERAGE AMOUNT'
from withbite
where line=1
group by account,
issuedate,
reference,
amount
0
source to share