SQL Aggregate Transactions by detail & Month

how to create aggregate tables in sql server
sql aggregate functions group by
group by sql
how to aggregate data in sql
sql aggregate first
sql order by aggregate function
sql aggregate multiple columns
sql joins with aggregate functions examples

I am trying to aggregate the total amounts of certain items. For example, I want to get the total amount for all transactions that have a trx_desc of "Amazon" and were in the month of August, for example.

This is the query that I used to try and output this data:

select det_trx_type, SUM(amount) as 'Total Amt',
CASE
    when MONTH(posting_date) = 8 THEN 'August'
    when MONTH(posting_date) = 9 THEN 'September'
    when MONTH(posting_date) = 10 THEN 'October'
END
as 'Month'

from av_finance 
where det_trx_type IN(
select distinct det_trx_type from av_finance)
group by  posting_date, det_trx_type

Data from query

I would like the total of Amazon purchases in the Month of August to show up on 1 row rather than multiple rows for each purchase in a given month.

You can try to let CASE WHEN in group by instead of only posting_date column because posting_date seems to be date data type.

select det_trx_type, SUM(amount) as 'Total Amt',
CASE
    when MONTH(posting_date) = 8 THEN 'August'
    when MONTH(posting_date) = 9 THEN 'September'
    when MONTH(posting_date) = 10 THEN 'October'
END as 'Month'
from av_finance 
where det_trx_type IN(select distinct det_trx_type from av_finance)
group by  
CASE
    when MONTH(posting_date) = 8 THEN 'August'
    when MONTH(posting_date) = 9 THEN 'September'
    when MONTH(posting_date) = 10 THEN 'October'
END, det_trx_type

The Art of Aggregating Data in SQL from Simple to Sliding , This article discusses the aggregation of data in SQL and the types of of all the customers along with their details then aggregated data of the  Aggregate functions in SQL are used for performing calculations on an entire column(which could have thousands of rows). They are generally used for summarizing large datasets. If you are familiar with Excel, then these functions are similar to the ones which we have in Excel.

Stop grouping by posting_date, group by the CASE expression too (or derive that in a CTE or subquery). Here is a CTE example:

;WITH x AS
(
  select det_trx_type, amount,
  CASE
    when MONTH(posting_date) = 8 THEN 'August'
    when MONTH(posting_date) = 9 THEN 'September'
    when MONTH(posting_date) = 10 THEN 'October'
  END as [Month] -- don't use ' for aliases!
  from dbo.av_finance  -- always use schema!
)
SELECT [Month], det_trx_type, SUM(amount)
FROM x
GROUP BY [Month], det_trx_type;

Also I removed the where in...distinct clause. I'm not sure what purpose it served.

Aggregate Functions (Transact-SQL), SQL Server Yes Azure SQL Database Yes Azure Synapse Analytics (SQL DW) Yes Parallel Data Warehouse. An aggregate function performs  APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.

I think you can try to aggregate in this way:

select det_trx_type, SUM(amount) as 'Total Amt',
    CASE
        when MONTH(posting_date) = 8 THEN 'August'
        when MONTH(posting_date) = 9 THEN 'September'
        when MONTH(posting_date) = 10 THEN 'October'
    END
    as 'Month'

    from av_finance 
    where det_trx_type IN(
    select distinct det_trx_type from av_finance)
    group by  MONTH(posting_date), det_trx_type

Overview of Aggregate Functions, Overview of Aggregate Functions - Functions that evaluate all of the values of a column and Refer to the DISTINCT clause for details. InterSystems SQL allows you to specify an aggregate function with other SELECT items in a query. to issue a transaction lock on all of the rows involved in an aggregate calculation. Aggregate functions in SQL In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.

Use SQL to Calculate a Running Total, For each subsequent invoice within the transaction date, the RunningTotal should For this step, we'll get the details set up so that we can calculate the running total. This is surprising, typically aggregate functions, such as SUM, require a  A transaction is a sequence of operations performed (using one or more SQL statements) on a database as a single logical unit of work. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database). A database transaction must be atomic, consistent, isolated and durable.

Learn SQL: Aggregate Functions, I won't go into details, but rather mention that all 6 tables in the model contain data. Some of the records in tables are referenced in others, while  Transact-SQL statements: Use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements to delineate transactions. API functions and methods: Database APIs such as ODBC, OLE DB, ADO, and the .NET Framework SqlClient namespace contain functions or methods used to

SQL Aggregate Functions and Group by, Practice with solution of exercises on SQL Aggregate Functions and Write a SQL statement to display customer details (ID and purchase  SQL's ROLLUP operator displays an extra record, a subtotal, for each group. That record is the result of evaluating all the records within each group using an aggregate function.

Comments
  • Stop grouping by posting_date, group by the CASE expression too (or derive that in a CTE or subquery).
  • What is the purpos of where det_trx_type IN...?
  • @Sami My intention was to get only the distinct (only 1 of each) transaction types (det_trx_type). In hopes that the output would limit the rows to have only 1 of each transaction types rather than multiple. My logic was flawed. I see that now.
  • Thanks! This worked. When you say "don't use ' for aliases, always use schema" what is an example of the schema you used in your query. Is it the ;WITH x as...?
  • ' for aliases is deprecated in certain forms (see this post) and can easily be misinterpreted as a string literal. The schema I'm talking about is the container for objects. See this post and this post.