Cumulative SQL Table

Question

I have an Excel file full of "Award Recipients", so like:

Name     | Date       | Category
John       May-1        Fishing
Mary       May-2        Swimming
Sue        Jun-6        Fishing

I am using this excel table as input into an SQL visualization program called "QlikView". The above is a very simplified example, the columns have different names.

I now want to make a virtual table of cumulative data

Category   | Date      | Total Awards Given
Fishing      May-1       1
Fishing      Jun-6       2
Swimming     May-2       1

I would be happy to be referred to an already asked question, but I am unfamiliar with most of the language on the "Similar Questions" sidebar.

Thanks SO!

also the output doesnt make sense given the input. Do you want a running total? — Woot4Moo, Jul 08 '13 at 16:35
It's an SQL question. I am processing this Excel Table with an SQL-based Visual Dashboard program. — RodericDay, Jul 08 '13 at 16:37
@RBarryYoung: in SQL a "virtual" table could also be a "derived table" or a "common table expression". — , Jul 08 '13 at 16:40
@a_horse_with_no_name: I'm pretty sure that it's the other way around. "Virtual Table" is a term from the orginal ANSI SQL spec, that refers to a specific type of object, a View. Wheras a "derived table" is a more general term referring to any query's clause or sub-expression that returns a rowset, including CTEs, subqueries and View references. But my ISO SQL doc is at home, so I'll have to wait until tonight tonight to check it... — RBarryYoung, Jul 08 '13 at 16:43
Honestly I usually do GUI Development on Python and a customer just needs some help with QlikView. I am sorry I am not an expert with the terminology, but QlikView doesn't seem to be very "pure" in many ways. Thanks to everyone who provided answers and clarifications. Not so many thanks for those who are extremely hostile to some ignorance. I thought that's what SO was for. — RodericDay, Jul 08 '13 at 16:45
RodericDay: My original response was not intended to convey any hostility, it was intended to clarify the term to anyone who might answer but was unaware what it meant (it's an uncommon term in SQL, which is probably why @a_horse_with_no_name and I are quibbling over its definition). — RBarryYoung, Jul 08 '13 at 16:49
You're cool. It's more the two immediate downvotes + the suggestion that I may be a dumbass who tagged an Excel question SQL for no reason. — RodericDay, Jul 08 '13 at 16:50
@RBarryYoung: hmm, interesting. I just searched the ANSI specs (2006). The word virtual does not appear at all in there - which I do find a bit confusing. Maybe my PDF reader is broken ;) — , Jul 08 '13 at 16:56
@a_horse_with_no_name The term may be archaic, I haven't heard it used formally in quite a while. — RBarryYoung, Jul 08 '13 at 16:58
I assume you want a QlikView solution. You can do this by adding a diagram and selecting 'Pivot-Table' in QlikView. I will add the complete anwser when it is possible again. — smartmeta, Jul 09 '13 at 05:53

score 1 · Answer 1 · answered Jul 08 '13 at 16:38

1

Since you asked for a SQL solution, this is ANSI SQL:

select category, 
       date,
       count(*) over (partition by category) as total_awards,
       count(*) over (partition by category order by date) as total_awards_so_far,
from the_table
order by category, date;

This assumes that Date (which is horrible name for a column) is of datatype date and thus can be sorted properly.

answered Jul 08 '13 at 16:38

It's not actually called "Date". This is a simplified example. "originalSubscriptionDate" is the real thing- still bad? – RodericDay Jul 08 '13 at 16:46
2

@RodericDay *originalSubscriptionDate* is fine as a column name ;) – Jul 08 '13 at 16:53

score 0 · Answer 2 · answered Jul 08 '13 at 16:39

0

Like this (ISO SQL):

CREATE VIEW CumulativeAwards As
    SELECT Category, "Date", COUNT(*) As "Total Awards Given"
    FROM   "Award Recipients"
    GROUP BY Category, "Date"

answered Jul 08 '13 at 16:39

RBarryYoung

55,398
14
96
137

Cumulative SQL Table

2 Answers2