Find amount greater than the average amount

Question

enter link description here

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

Answer 1

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

Answer 2

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.