I doubt that I can give you a definitive answer to this question, but I will give you my thoughts on it. Comparing 2 Samuel 24 and 1 Chronicles 21 to get the full account:
2 Samuel 24:1 And again the anger of the LORD was kindled against Israel,
This ‘again’ follows the previous occasion of God’s anger with Israel which involved 3 years of famine before its resolution in the fourth year. This may explain the apparent discrepancy between 7 years famine (ie 3 years famine past + the 4th year when it was resolved + 3 more years for the current offence) in 2 Samuel 24:13 and 3 years famine in 1 Chronicles 21:12.
2 Samuel 21:1 Then there was a famine in the days of David three years, year after year; and David enquired of the LORD. And the LORD answered, It is for Saul, and for his bloody house, because he slew the Gibeonites.
The prime cause is that God was angry with Israel.
2 Samuel 24:1... he moved David against them... = 1 Chronicles 21:1... Satan... against Israel... provoked David...
‘Satan’ is not the fallen angel of Christian mythology. ‘Satan’ is anglicized Hebrew meaning ‘adversary’. The adversary would seem to be David’s own desire to have a grand total of the size of his army – the number of fighting men in all Israel that he had at his command. David was ruling for God but he seems to have overstepped the mark in numbering God’s people without God’s instruction or assent. We might reasonably suppose that the desire was due to David’s pride and this constituted sin on his part.
God tempts no man to sin, but what God permits he is said to do.
1 Samuel 26:19... If the LORD have stirred thee up against me, let him accept an offering...
Psalms 105:25 He turned their heart to hate his people...
Isaiah 63:17 O LORD, why hast thou made us to err from thy ways, and hardened our heart from thy fear?...
The lust that produces sin comes not from without but from within men.
Jeremiah 17:9 The heart is deceitful above all things, and desperately wicked: who can know it?
James 1:13 Let no man say when he is tempted, I am tempted of God: for God cannot be tempted with evil, neither tempteth he any man: 14 But every man is tempted, when he is drawn away of his own lust, and enticed. 15 Then when lust hath conceived, it bringeth forth sin: and sin, when it is finished, bringeth forth death.
God’s anger was not against David but Israel.
What Israel’s specific offence was we are not told, but it follows on the support given by the people to the two rebellions against God’s chosen king headed by Absalom and Sheba. One suggestion is that God punished Israel because the half shekel ransom was not collected as required in Exodus 30:12.
Exodus 30:12 When thou takest the sum of the children of Israel after their number, then shall they give every man a ransom for his soul unto the LORD, when thou numberest them; that there be no plague among them, when thou numberest them.
Whether this was the case or not, people do suffer the consequences of the sins of their rulers. This is evident from the three years famine that God brought upon Israel because of what Saul had done to the Gibeonites.
2 Samuel 21:1... the LORD answered, It (the famine) is for Saul, and for his bloody house, because he slew the Gibeonites.
Saul, of course, could not have done it alone. The people he commanded were complicit in the action.
God was angry with Israel at this time for a different reason. Joab recognised that David’s action would be a cause of trespass for Israel as a whole (if the half shekel was the problem he could have collected it).
1 Chronicles 21:3... why then doth my lord require this thing? why will he be a cause of trespass to Israel?
We are told that it was this ‘thing’ (the numbering of Israel to determine its fighting strength) that caused God’s anger to be turned into punishment.
1 Chronicles 21:7 And God was displeased with this thing; therefore he smote Israel.
That David’s subjects died because of his sin may seem to us unreasonable on God’s part, but we have to look at it from God’s point of view. God’s object is to save men and women not to destroy them.
Ezekiel 33:11... As I live, saith the Lord GOD, I have no pleasure in the death of the wicked; but that the wicked turn from his way and live: turn ye, turn ye from your evil ways; for why will ye die, O house of Israel?
Israel’s unfaithfulness was a continual problem from the time that they came out of Egypt.
Amos 3:1 Hear this word that the LORD hath spoken against you, O children of Israel, against the whole family which I brought up from the land of Egypt, saying, 2 You only have I known of all the families of the earth: therefore I will punish you for all your iniquities.
The punishments that they endured were designed to serve as a warning to all in order to bring men and women to repent of sin and serve God.
Romans 11:22 Behold therefore the goodness and severity of God: on them which fell, severity; but toward thee, goodness, if thou continue in his goodness: otherwise thou also shalt be cut off.
All men and women live under sentence of death because as individuals they sin. This life is temporary and carries no guarantee of either quality or longevity. Our manner of death does not affect the outcome of our resurrection. Not a single individual who faithfully obeys God in this life will lose eternal life at the resurrection and the kingdom of God.
Luke 12:7 But even the very hairs of your head are all numbered. Fear not therefore: ye are of more value than many sparrows.
Romans 1:17 For therein is the righteousness of God revealed from faith to faith: as it is written, The just shall live by faith.
For the faithful who died in the plague as a result of David’s sin, their next waking moment will be in the kingdom of God. I think that the lesson to be learned from the incident is that God punishes nations for the sinful acts of their rulers.
The Jewish rulers had Christ put to death and the nation has suffered collectively for it. In this respect they are God’s witnesses in that prophecy is being fulfilled in them to this day.
Isaiah 43:10 Ye are my witnesses, saith the LORD, and my servant whom I have chosen: that ye may know and believe me, and understand that I am he: before me there was no God formed, neither shall there be after me. 11 I, even I, am the LORD; and beside me there is no saviour.12 I have declared, and have saved, and I have shewed, when there was no strange god among you: therefore ye are my witnesses, saith the LORD, that I am God.
Faithful men and women who perish in the collective punishment of the nation will still inherit eternal life.
The good that came out of the evil was the designation of a place where the Temple of God would be built.
I hope you have found this helpful.
God bless you,