Hi,

I am attempting to build a new table from a MySQL query in order to generate a bar chart. I am able to manage the chart fine, and the connection to the MySQL table is established fine.

My question relates to the complication in the SQL statement. I have tested this SQL fine, and it sums for me the values in Regulator1 perfectly.

SELECT Regulator1, COUNT(DISTINCT uSimID) AS Count
FROM Simulators
WHERE Regulator1 IS NOT NULL AND Regulator1 != ""
GROUP BY Regulator1
ORDER BY Regulator1

The issue is that I have more than one column where I need to check for and count the DISTINCT values for. The above SQL is for Regulator1 alone, but I also have Regulator2, Regulator3, Regulator4 and Regulator5 columns to check.

My aim is to add up all the counts of distinct values for each of the 5 columns. The table is structured this way because one record in the database can carry more than one Regulator, hence the need for 5 columns. Up to 5 can be designated in each record.

I appreciate any suggestions and am grateful that this is not really an issue with ARI DataTables and goes beyond your support obligations. I just have no ideas myself and have searched extensively for guidance.

Ken

Thanks, but you can ignore this thread, I managed to find the solution by examining other SQL support sites for un-related but somewhat similar questions and selected testing.