Summarizing annual donations to charity; SQL answer

[Wm]

Tue, 30 Dec 2014 06:05:45 <54A28709.30804 at verizon.net>  Jean-David Beyer
<jeandavid8 at verizon.net>

>On 12/29/2014 09:56 PM, Jim Thompson wrote:
>> Greetings!
>>
>> Thinking about summarizing data for tax preparation - in particular,
>> deductible donations to charity.
>>
>> I can create an annual report for that expense account, with details and
>> totals by month and year.
>>
>> What I'd really prefer would be annual totals by Description...
>> - Charity A - $$$
>> - Charity B - $$$
>> etc.
>>
>
>What I do is have an expense account called Charity that I use in the
>normal way.
>
>To get the report you want, I run a "Transaction Report" with the start
>date at the start of the tax year (For me right now, I use 2014 January
>1) and end date at the end of the tax year (For me right now, I use 2014
>December 31). I select ONLY the Charity expense account, and sort on
>Description for the primary key, and Date for the secondary key. I sort
>both ascending, and do not let it do a subtotal.
>
>The reason I sort on Description is that I sometimes make more than one
>contribution to the same charity, and want these together. I do not know
>how to subtotal the amounts for items with the same Description, so I do
>that manually when using TaxAct for my taxes.
>
>Here are my first few entries for this year. Numbers fudged.
>
>> Date         Num     Description     Memo/Notes          Amount
>> 2014-08-23   0123    American Friends Service Committee $xxx.00
>> 2014-12-28           Amnesty International               $yy.50

If you (anyone reading this) are not interested in SQL ignore this,
otherwise this should produce something very close to what Jim and
Jean-David are after.

===
Select
  Sum(1.0 * splits.value_num / splits.value_denom) As Exp,
  accounts.account_type,
  accounts.name,
  transactions.description
From
  splits Inner Join
  accounts On accounts.guid = splits.account_guid Inner Join
  transactions On splits.tx_guid = transactions.guid
Where
  -- change Charity to your account name in the line below
  accounts.name = 'Charity' And
  -- adjust dates if necessary in the two lines below
  transactions.post_date <= '2014-12-31' And
  transactions.post_date >= '2014-01-01'
Group By
  accounts.account_type, accounts.name, transactions.description
Having
  accounts.account_type In ('EXPENSE')
Order By
  accounts.account_type,
  transactions.description
===

-- 
Wm...