I have an csv data dump which is a flattening of related tables from a sql database. So to simplify it looks like this.
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| A | Ad | B | B1 |
| A | Ad | B | B2 |
| A | Ad | B | B3 |
| A | Ad | C | C1 |
| A | Ad | C | C2 |
| X | Xx | D | D1 |
| X | Xx | D | D2 |
| X | Xx | E | E3 |
From this table I need to generate various graphs and pivots from subsets of this data
so I would like to generate (link) to this data and create tables that represent the queryable sets I am after eg.
| col1 | col2 |
+------+------+
| A | Ad |
| X | Xx |
and
| col1 | col2 | col3 |
+------+------+------+
| A | Ad | B |
| A | Ad | C |
| X | Xx | D |
| X | Xx | E |
mostly so I can do counts of the unique combinations of the flattened data.
It is important that when I refresh the data from the datasource that these tables update accurately.
So how do I do this?
EDIT
Your answers have been helpful, it seems my question wasn't quite good enough
for the first table I really want to produce this
| col1 | col2 | count
+------+------+------
| A | Ad | 1
| X | Xx | 1
and the second one
| col1 | col2 | col3 | count
+------+------+------+------
| A | Ad | B | 1
| A | Ad | C | 1
| X | Xx | D | 1
| X | Xx | E | 1
So the counts reflect the distinct record count at a given level not the sum of all rows.
I need to answer questions like "what is the total of distinct items in col1"
I also need to answer this question "show count of col3 for col1"
I expect I will be asked to make graphs and present the data at various normalized levels too.
I hope this is more accurate for you. Thanks for the help so far