嵌套求和解决方案技术求助 | SQL | Access

Hey there! Let's break down what's going wrong with your current SQL query and fix it step by step—since you're new to SQL, we'll keep this clear and straightforward.

What's Wrong With Your Current Query? #

Your existing query uses a WHERE clause that compares each row's own Year value to its own Subs. – Date of incorporation before counting. That means it's only counting rows where the row's year is <= its own subsidiary's incorporation date, which isn't what you want.

Your actual goal is:

For each Shareholder and Year, count how many of their subsidiaries were incorporated on or before that Year.

The Corrected Query #

Here's a beginner-friendly solution using a correlated subquery that does exactly what you need:

SELECT
    YEAR(t1.[Year]) AS [Year],
    t1.Shareholder,
    (
        -- This subquery counts all subsidiaries for the same shareholder
        -- where the incorporation year is <= the current outer query's year
        SELECT COUNT(*)
        FROM Table1 t2
        WHERE t2.Shareholder = t1.Shareholder
          AND YEAR(t2.[Subs. – Date of incorporation]) <= YEAR(t1.[Year])
    ) AS [No of Subs]
FROM Table1 t1
GROUP BY t1.Shareholder, YEAR(t1.[Year])
ORDER BY t1.Shareholder, YEAR(t1.[Year]);

How This Works #

1. Outer Query: We first group your data by Shareholder and Year (using YEAR() to extract the year value if your Year column is a full date). This gives us a unique row for each shareholder-year pair.
2. Correlated Subquery: For each of those shareholder-year pairs, the inner query scans the entire table, filters for the same shareholder, and counts only those subsidiaries where their incorporation year is <= the current outer query's year.

Example of Correct Results #

If Shareholder ABC has two subsidiaries: one incorporated in 2013, another in 2014, your results will now look like this:

• 2013 | ABC | 1
• 2014 | ABC | 2
• 2015 | ABC | 2

Which matches the expected cumulative count (no more incorrect "2" values across all years!).

Quick Notes #

• If your Year column is already an integer (not a full date), you can remove all YEAR() function calls to simplify the query.
• The GROUP BY ensures we only get one row per shareholder-year, even if your original table has multiple duplicate rows for the same pair.

内容的提问来源于stack exchange，提问作者Fred Rik