CODEWITHSUNDEEP
sqlms-access
alasdairvfr
alasdairvfr

Reputation: 35

SQL (Access) Queries to create a chart

I have been building out queries and creating stats based on a daily query run. Numbers change frequently so this is the same run, daily to generate a report. I am pretty happy with the results but lack the SQL skills to do anything other than run multiple queries that give me 4 daily stats per logical group.

The queries count the total (mostly static) then numbers of different types of activity which will always be a smaller number than the previous (always requisite) value. I have groups and subgroups which I select and display as 4 numbers. Here is an example of the raw data:

+-------+------+-------+-------+---------+
| Group | born | 1-20  | 20-65 | 65+     |
+-------+------+-------+-------+---------+
|     1 | yes  | yes   | yes   | yes     |
|     1 | yes  | yes   | yes   |         |
|     1 | yes  |       |       |         |
|     1 | yes  | yes   |       |         |
|     2 | yes  | yes   | yes   | yes     |
|     2 | yes  | yes   | yes   |         |
|     2 | yes  | yes   |       |         |
|     2 | yes  |       |       |         |
|     2 | yes  | yes   |       | yes     |
|     3 | yes  |       |       |         |
|     3 | yes  | yes   |       |         |
|     3 | yes  | yes   | yes   | yes     |
|     3 | yes  |       |       |         |
+-------+------+-------+-------+---------+

So what I have been doing is multiple queries of:

Select count(born) AS Alive, count(1-20) AS Child, count(20-65) AS Adult, count(65+) AS Elderly
FROM table
WHERE Group = "1";

Then I get my stats for Group 1:

+-------+-------+-------+---------+
| Alive | Child | Adult | Elderly |
+-------+-------+-------+---------+
|     4 |     3 |     2 |       1 |
+-------+-------+-------+---------+

Then I do it for Groups 2 - n and I manually input the results in a different spreadsheet. I'm coming to the point where I've scripted over 100 of these in a day so would like to create a series of exportable tables that would display the information like this:

+---------+---+---+---+
|  Group  | 1 | 2 | 3 |
+---------+---+---+---+
| born    | 4 | 5 | 4 |
| child   | 3 | 4 | 2 |
| adult   | 2 | 2 | 1 |
| elderly | 1 | 1 | 1 |
+---------+---+---+---+

I have tried a number of things like case + sum queries but either the syntax is wrong or my ability to find online examples is limited to things that aren't what I need.

The example isn't ideal since it's abnormal to count people's age this way but I need each requisite value tallied, so an "elderly" person needs to show up as 4/4 possible values.

Thank you in advance!

Upvotes: 1

Views: 116

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Do you just want group by?

Select [group], count(born) AS Alive, count([1-20]) AS Child, count([20-65]) AS Adult, count([65+]) AS Elderly
FROM table
GROUP BY [group];

Note that group is a very poor name for a column because it is a SQL keyword.

Upvotes: 1

Related Questions