DB2中实现门店年度、月度数据同比并排对比的SQL查询需求

Hey there! I see you want to compare store totals side-by-side for current vs previous year (and current month vs same month last year). Your current query pulls daily data, so we’ll adjust it using conditional aggregation to get the grouped, side-by-side totals you’re looking for.

1. Year-over-Year Comparison #

To calculate each store’s total value for the current year vs the previous year, use SUM() paired with CASE statements to filter dates accordingly. Here’s the adjusted query:

SELECT
    STORE,
    SUM(CASE WHEN EXTRACT(YEAR FROM DATE) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TOTAL ELSE 0 END) AS VALUE_CURRENT_YEAR,
    SUM(CASE WHEN EXTRACT(YEAR FROM DATE) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN TOTAL ELSE 0 END) AS VALUE_LAST_YEAR
FROM MYTABLE
GROUP BY STORE
ORDER BY STORE;

Quick Breakdown: #

• EXTRACT(YEAR FROM DATE) pulls the year from your date column. Adjust this function if your SQL dialect uses something else (e.g., YEAR(DATE) for MySQL, DATEPART(YEAR, DATE) for SQL Server).
• The first CASE statement sums only TOTAL values from the current year.
• The second CASE statement sums values from the immediately preceding year.
• Grouping by STORE ensures you get one row per store with both yearly totals.

Using your sample data (all dates in 2018), the result would match your example format:

STORE | VALUE_CURRENT_YEAR | VALUE_LAST_YEAR
1     | 60                 | 30
2     | 40                 | [2017 total for store 2]

2. Month-over-Year (Same Month Last Year) Comparison #

If you want to compare the current month’s total to the same month last year, tweak the CASE conditions to target specific year-month pairs:

SELECT
    STORE,
    SUM(CASE 
        WHEN EXTRACT(YEAR FROM DATE) = EXTRACT(YEAR FROM CURRENT_DATE) 
             AND EXTRACT(MONTH FROM DATE) = EXTRACT(MONTH FROM CURRENT_DATE) 
        THEN TOTAL ELSE 0 END) AS VALUE_CURRENT_MONTH,
    SUM(CASE 
        WHEN EXTRACT(YEAR FROM DATE) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 
             AND EXTRACT(MONTH FROM DATE) = EXTRACT(MONTH FROM CURRENT_DATE) 
        THEN TOTAL ELSE 0 END) AS VALUE_LAST_YEAR_SAME_MONTH
FROM MYTABLE
GROUP BY STORE
ORDER BY STORE;

Quick Breakdown: #

• This query adds a month check to ensure you’re only summing values from the current month (current year) and the exact same month from the previous year.
• For example, if today is March 27, 2018, it sums March 2018 totals vs March 2017 totals for each store.

内容的提问来源于stack exchange，提问作者Felipe Mendes