Is there an easy way to get details about a csv file and how?

Question

I have a csv that I would like to gather details about using Coldfusion

I have 2 columns that I would like to gather details for: -Comapny -Amount

The details that I would like to gather are: -I would like to select each Distinct Company -I would like to select how many rows there are for each Company -I also want to Select the SUM of amount for each distinct Company

What would be the best and fastest way to achieve this?

Thank you in advance

Answer 1

From your previous question you know about using cfhttp to get a query object. Your query of queries becomes

select company, count(company) CompanyCount, sum(amount) CompanyAmount
from YourQueryObject
group by company

Edit begins here

Since the amount is a varchar, you'll have to cast it.

select company, count(company) CompanyCount, sum(cast amount as decimal) CompanyAmount
from YourQueryObject
group by company

However, before you do that, you might want to do this:

 <cfset QueryAddColumn(YourQueryObject, 'IsGoodRecord', ArrayNew(1))>
 <cfloop query="YourQueryObject">
 validation code, plus set IsGoodRecord to 0 or 1
 </cfloop>

Then add

 where IsGoodRecord = 1

to your query of queries.