Define a temporary table within an SQL "select" query-CodePudding

I'm referring to MS Access SQL. Suppose I have a column with dates of birth of a population and the decades which these dates fall into.

Year Decade
1971 8
1953 6
1958 6
1929 3
1930 4

I want to create a query which will show how many people were born in each decade of a century.

I know it's going to be something like

SELECT (Year \ 100) mod 10 as [Decade], Count(*) as [How many people]
FROM People
GROUP BY (Year \ 100) mod 10

My problem is that there might be some decades in which no one was born from my population and I still want these to show up in my query, with a zero.

My ideal solution would be defining a table on the fly, consisting of rows {1,2,3,4…}, very much like you'd do in any programming language, say in Python decades = range(1,10), then creating the table with the counted people, and then joining these two together with a left join.

It seems not possible, but I'm a newbie to SQL and databases. Is that possible? What are other approaches?

CodePudding user response：

MsAccess does not have a function like Range() that you can use. What I have done in my databases is create a table of numbers to use for cases like this.

The simplest way to create this table is by using an Excel spreadsheet to build the column of numbers (for instance, from 1 to 1,000) and then import the spreadsheet as a new table. Then make whatever adjustments are appropriate - for example, the new table should have a primary key on the numbers column, and the numbers column should probably be of a long integer data type. You could call the table [Numbers] and name the column [NumberValue] - these names are up (you could just as easily call your column [Nums] or even just [N]). But I would caution against using the name [Number] for a table or column because Number is a datatype name and MSAccess does not always play nicely with names that are SQL or VBA keywords.

Now you can use your new table with regular sql: Select * from [Numbers] where NumberValue >= 1 and NumberValue <= 10

CodePudding user response：

First, create a small query to generate numbers:

Select Distinct Abs([id] Mod 10) As N
From MSysObjects;

Save it as Ten.

Then create a Cartesian (multiplying) query like this:

Select 
    Ten.N As Decade, 
    Val(Nz(T.C)) As [How many people]
From 
    Ten
Left Join
    (Select Count(*) As C, [Year] Mod 10 As D
    From People
    Group By [Year] Mod 10) As T
    On Ten.N = T.D

Output will be similar to: