Grouping, Counting, Totally Query Output

Good day everyone. I'm hoping someone can point me in the right direction. I've been using Coldfusion for awhile, although I admit, I'm not a master at it.

I'm pulling my hair out on a project I'm hoping someone can shed some light on.

I have a table in my database that I want to group and output a report on.

It's a table tracking purchases and types of purchases.

Here's the data I'm collecting: from_name, from_company, transaction_type, and amount. I have a few other fields, but they aren't needed for this report. There are multiple transactions per person and a varying number of persons.

What I need to do is to create queries that will group by name or company, which doesn't matter, give me a count of transaction types (3 of A, 6 of B, etc.) as well as a total amount of each type, then show the total transactions count by name or company and the total amount.

I'm trying to output in a table having each row be the data from each company.

Can someone point me in the right direction to group such output, do counts on the number of records as well as show totals dollars?

Thank you so much for any advice you might have.

 

Try the query as follows:

<cfset groupBy="from_name"> <!--- or from_company --->

<cfquery name="test" datasource="DSN">
SELECT #groupBy#,transaction_type count(transaction_type) Transactions, sum(amount) TotalAmount
FROM table
GROUP by #groupBy#,transaction_type
ORDER BY #groupBy#,transaction_type
</cfquery>

Code (markup):

 

Watch out for that kind of dynamic sql. It can present sql injection risks, depending on the source of #groupBy#.

 

Good point, wrap the groupBy in a CFQueryParam tag as

<cfqueryparam value="#groupBy#" cfsqltype="cf_sql_varchar">

Code (markup):

 

Unfortunately, it only works for values. Not column or table names But I don't think even need dynamic columns anyway. I think the original post said name OR company. So hard coded should work.