Help with SQL please - adding column contents

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
rogerhwatts
Posts: 44
Joined: Mon Mar 21, 2011 5:22 pm

Help with SQL please - adding column contents

Post by rogerhwatts »

Hi,
I have a membership database. Each year we have column for the Date paid (eg Bank2014) and the amount paid ( eg Amt2014).
We run a calendar year membership but members can pay up to 2 months early. However, for Gift Aid we need any subs banked in the given Calendar year, regardless of which subs year they are for.
So, I need a Base Query column which calculates (Bank2014>=1/1/14)* Amt2014 + (Bank2015< 1/1/15)* Amt2015

I'm sure this must be quite simple.

regards
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Help with SQL please - adding column contents

Post by MTP »

I think you will need two (sub)queries: one to sum over the rows where Bank2014>=1/1/14 and one to sum over the rows where Bank2015<1/1/15:

Code: Select all

SELECT "A"."2014_Subs" + "B"."2015_Subs" AS "2014_Banked_Subs"
   FROM (SELECT SUM("Amt2014") AS "2014_Subs" FROM "YourTableName" WHERE "Bank2014" >= '2014-01-01') "A", 
   (SELECT SUM("Amt2015") AS "2015_Subs" FROM "YourTableName" WHERE "Bank2015" < '2015-01-01') "B"
Not really that simple. This would be much easier if you stored your membership dues in rows, in a separate table, instead of in columns in your main table. For example, you could have the following tables where TABLE NAME is in all caps and column names for each table are listed under the name:

MEMBERS
MemberID (primary key)
MemberName
MemberAddress
[Other member identifying information]

SUBSCRIPTIONS
SubID (primary key)
MemberID (foreign key - meaning, it matches a primary key from the MEMBERS table)
SubYear
PaymentDate
AmountPaid


Now, if your database was set up like that, your query would be:

Code: Select all

SELECT SUM("AmountPaid") 
   FROM "SUBSCRIPTIONS" 
   WHERE "PaymentDate"  >= '2014-01-01' AND "PaymentDate" < '2015-01-01'
Much simpler, yes?
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
rogerhwatts
Posts: 44
Joined: Mon Mar 21, 2011 5:22 pm

Re: Help with SQL please - adding column contents

Post by rogerhwatts »

Thanks. I will try that out.
I realise that this would be much easier if I had a separate Table for subs ie a relational database. My problem is that our group (1000 strong) is generally elderly and few (admit to ) knowing anything about databases. So I keep it so that, if the worst came to the worst, the single main table could be copied (using fairly simple instructions) as a spreadsheet which maybe someone else would be able to comprehend.
OpenOffice 4.00
Windows 7
Post Reply