Skip to main content
Solved

Email Summary Query Results in HTML Table


bill7875
Contributor
Forum|alt.badge.img+2

I have a summary query with GROUP BY, HAVING, and ORDER BY clauses and I would like to send an email with the results from this query. My workspace can send html results of a query of listing all the records but that is not the summary I’m looking for. The Aggregator transformer doesn’t seem to have what I’m looking for either. Is there a way to send the result of this query shown below? My data is in SQL Server.

  SELECT caseID 'ID', count(*) 'Count'
  FROM my_table
  group by caseID
  having count(*) > 1
  order by count(*) desc

 

Best answer by hkingsbury

have a look at the statisticscalculator and set the groupby to ‘caseID’. This will allow you to get a count of all records with the same caseID. You can then use a tester to filter out those that only have a count of 1

View original
Did this help you find an answer to your question?

4 replies

hkingsbury
Celebrity
Forum|alt.badge.img+53
  • Celebrity
  • August 1, 2024

Can you provide a high level example of the data you’re getting out of SQL, and what you want the resulting email to look like?


bill7875
Contributor
Forum|alt.badge.img+2
  • Author
  • Contributor
  • August 1, 2024

This is what I would like the result to look like 

 

The data are records with a key of caseID. There could be more than one record for a given caseID. I want to only see the cases that have multiple records and the count of how many records for that caseID.


hkingsbury
Celebrity
Forum|alt.badge.img+53
  • Celebrity
  • Best Answer
  • August 5, 2024

have a look at the statisticscalculator and set the groupby to ‘caseID’. This will allow you to get a count of all records with the same caseID. You can then use a tester to filter out those that only have a count of 1


bill7875
Contributor
Forum|alt.badge.img+2
  • Author
  • Contributor
  • August 5, 2024

That works perfectly. I used the StatisticsCalculator, Tester and the Sorter and it gives me exactly what I need. Thank you very much for your help.


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings