N. Ziff N. Ziff - 5 months ago 17
SQL Question

CFChart producing a 500 Error

I was creating a chart with a query of queries, and I was receiving a 500 error no matter what I did. I ended up scoping down the cfchart tag as the problem, but I couldn't find any syntax errors in it, nor any logical errors. Here is the code:

<cfquery name="getData" datasource="#dsn#">
SELECT
(SELECT Count(UserID) FROM Users WHERE StripeCustomerID IS NOT NULL) AS 'Licenses',
COUNT(UserID) AS 'Registrations',
UserID AS 'User'
FROM Users
WHERE DATEDIFF(hour, FirstContact, DateStamp) <= '12'
GROUP BY UserID
</cfquery>

<cfquery name="queryOfQuery" dbtype="query">
SELECT (Licenses/Registrations) AS Percent FROM getData
</cfquery>


<cfchart format="flash">

<cfchartseries type="bar"
query="queryOfQuery"
itemcolumn="UserID"
valuecolumn="Percent" />

</cfchart>

Answer

Your syntax issue is

SELECT (Licenses/Registrations) AS Percent FROM getData

Percent is a reserved key word so you need to add brackets or single quotes to let the SQL engine know you are using a reserved keyword as a column alias.

SELECT (Licenses/Registrations) AS [Percent] FROM getData

OR

SELECT (Licenses/Registrations) AS 'Percent' FROM getData

You can add percent column to your original dataset easily.

SELECT 
    (SELECT Count(UserID) FROM Users WHERE StripeCustomerID IS NOT NULL)     AS     Licenses, 
    COUNT(UserID) AS Registrations,
    (SELECT Count(UserID) FROM Users WHERE StripeCustomerID IS NOT NULL) / COUNT(UserID) AS [Percent],
    UserID AS User
FROM Users
WHERE DATEDIFF(hour, FirstContact, DateStamp) <= '12'
GROUP BY UserID

Plus I am curious what you want for Licenses. Are you trying to get DISTINCT UserId count or count of all non null UserIds. The rest of your query suggests that USerId repeats itself in the Users table (which I find very odd)... So UserId 5 might exist 2 or 3 times, so counting distinct would give you 1 for userid 5 while not adding distinct would give you the 2 or 3...

Are registrations always 1? If so then UserId is unique and you don't need your aggregations in your main query at all. And the answer for Percent will always be the same as 1/# of Licenses.......