SQL Server Find amount greater than the average amount

zlhcx6iw 于 12个月前发布在其他

关注(0)|答案(2)|浏览(115)

I want to show expense types that have a half year spend amount greater than the average half‐year expense type spend for each of the half‐years April‐September and October‐March.

Sample data (associated fiddle https://dbfiddle.uk/G6b9TKhB ):
| Expense_Type | Amount | Date_Of_Payment |
| ------------ | ------------ | ------------ |
| Consultancy Expenditure | 5000.00 | 2022-05-15 |
| Criminal Law | -2500.50 | 2022-08-22 |
| External Training | 7500.75 | 2022-11-10 |
| Consultancy Expenditure | 12000.00 | 2023-01-05 |
| External Training | -5000.25 | 2023-04-18 |
| Criminal Law | 80000.00 | 2023-07-02 |
| Consultancy Expenditure | -3000.50 | 2023-09-14 |
| External Training | 6000.25 | 2023-12-28 |
| Criminal Law | 3500.00 | 2023-02-08 |
| Criminal Law | -10000.00 | 2022-06-30 |
| Consultancy Expenditure | 9500.00 | 2022-04-20 |
| External Training | -7500.75 | 2022-10-05 |
| Criminal Law | 3000.50 | 2022-12-15 |
| Consultancy Expenditure | -18000.00 | 2023-01-25 |
| External Training | 2000.25 | 2023-03-12 |
| Criminal Law | -60000.00 | 2023-05-28 |
| Consultancy Expenditure | 4500.50 | 2023-08-07 |
| External Training | 3000.75 | 2023-10-19 |
| Criminal Law | 12000.00 | 2023-01-02 |
| Criminal Law | -5000.00 | 2022-07-15 |
| Consultancy Expenditure | 6000.00 | 2022-09-28 |
| External Training | -3000.25 | 2023-11-10 |
| Criminal Law | 9000.50 | 2023-04-01 |
| Consultancy Expenditure | -12000.00 | 2023-06-14 |
| External Training | 1500.25 | 2023-09-26 |
| Criminal Law | 5500.00 | 2023-12-09 |
| Consultancy Expenditure | 8000.50 | 2023-02-22 |
| External Training | -4500.75 | 2022-05-03 |
| Criminal Law | 18000.00 | 2022-08-17 |
| Consultancy Expenditure | -3000.00 | 2022-11-30 |
| External Training | 2500.75 | 2023-02-14 |
| Criminal Law | -7500.50 | 2023-05-01 |
| Consultancy Expenditure | 7000.25 | 2023-08-14 |
| External Training | -2000.25 | 2023-10-27 |
| Criminal Law | 4000.00 | 2023-01-09 |
| Consultancy Expenditure | -6000.50 | 2022-07-22 |
| External Training | 3500.75 | 2022-09-04 |
| Criminal Law | 15000.00 | 2023-11-17 |
| Consultancy Expenditure | 2000.50 | 2023-04-05 |
| External Training | -1200.25 | 2023-07-18 |
| Criminal Law | -3000.00 | 2023-09-30 |
| Consultancy Expenditure | 10000.25 | 2023-12-13 |
| External Training | 5000.00 | 2023-02-28 |
| Criminal Law | -4500.50 | 2022-06-13 |
| Consultancy Expenditure | 3000.75 | 2022-10-26 |
| External Training | -2500.75 | 2022-12-08 |
| Criminal Law | 6000.00 | 2023-03-23 |
| Consultancy Expenditure | -1500.00 | 2023-06-06 |
| External Training | 1200.25 | 2023-09-19 |
| Criminal Law | 2500.50 | 2023-12-02 |

I have tried this SQL:

select 
    avgvalue > average   -- I want to show all expense type greater than average.
from 
    (select 
         sum(Amount) as avgvalue,
         avg(sum(Amount)) as average
     from 
         mydatatable
     where
         (DateofPayment like '%/04/%' or
          DateofPayment like '%/05/%' or 
          DateofPayment like '%/06/%' or 
          DateofPayment like '%/07/%' or
          DateofPayment like '%/08/%' or
          DateofPayment like '%/09/%') 
     group by 
         ExpenseType) z

I found avg amount and sum of same expense type. But did not know how to do. please help

I need code to get all expense type in months April to September. First sum all same expense type to make expense type unique. then take average of all sum amount then show all expense type with an amount that is greater than average

sql-server

来源：https://stackoverflow.com/questions/77614708/find-amount-greater-than-the-average-amount

2条答案

按热度按时间

lstz6jyr1#

You can use a window function AVG(...) OVER () to average all values in the table.

SELECT
 *
FROM (
    SELECT
      ExpenseType,
      SUM(Amount) AS sum,
      AVG(SUM(Amount)) OVER () AS average
    FROM mydatatable
    WHERE DATEPART(month, DateofPayment) BETWEEN 4 AND 9 
    GROUP BY ExpenseType
) z
WHERE avgvalue > average

赞(0）回复(0）举报 12个月前

rdrgkggo2#

If this were SQL Server 2022, I would suggest using the DATE_BUCKET() function to cleanly map the source dates into 6-month buckets.

For earlier versions of SQL server, you can use the following calculation:

DATEADD(
    month,
    DATEDIFF(month, '1900-04-01', DateOfPayment ) / 6 * 6,
    '1900-04-01'
    ) AS DateBucket

Here, '1900-04-01' is reference date used to define the start of some arbitrary 6-month window (bucket). The / 6 * 6 is used to round the month number down to the prior 6-month multiple.

This can then be wrapped up in a CROSS APPLY and used in later GROUP BY or window function PARTITION BY conditions.

The resulting query would be something like:

select
    DateBucket as FromDate,
    dateadd(day, -1, dateadd(month, 6, DateBucket)) as ThruDate,
    Expense_Type,
    SumAmount,
    AvgSumAmount
from (
    select
        DateBucket,
        Expense_Type,
        sum(Amount) as SumAmount,
        avg(sum(Amount)) OVER(PARTITION BY DateBucket) AS AvgSumAmount
    from ExpenseTable E
    cross apply (
        select dateadd(
            month, 
            datediff(month, '1900-04-01', Date_Of_Payment ) / 6 * 6,
            '1900-04-01'
            ) AS DateBucket
    ) b
    group by DateBucket, Expense_Type
) a
where SumAmount > AvgSumAmount
order by DateBucket, Expense_Type;

The inner query calculates the 6-month date bucket and then groups the expense data by date bucket and expense type, summing the amount in the process. It also calculates the average-total-expense for each date bucket using a window function. The top level then just applies the final filter condition for the results.

Results:
| FromDate | ThruDate | Expense_Type | SumAmount | AvgSumAmount |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 2022-04-01 | 2022-09-30 | Consultancy Expenditure | 14499.50 | 3166.166666 |
| 2022-10-01 | 2023-03-31 | Criminal Law | 28500.50 | 12500.666666 |
| 2023-04-01 | 2023-09-30 | Criminal Law | 18500.00 | 4000.250000 |
| 2023-10-01 | 2024-03-31 | Criminal Law | 23000.50 | 12333.750000 |

See this db<>fiddle or this one for a demo.

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Find amount greater than the average amount

2条答案

相关问题

热门标签

最新问答