CFChart producing a 500 Error

Question

I am trying to create a chart with a query of queries, but am receiving a 500 error no matter what I try. I ended up scoping down the cfchart tag as the problem, but couldn't find any syntax errors in it, nor any logical errors.

<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>

Sample Data

Users Table Sample Data
UserID    StripeCustomerID
35         cus_8LOSlmvEQcfg1g
36         cus_6ixCBe7aU1MJEt
50         NULL
60         cus_6ifLCNmR9rOBCl
69         NULL
70         NULL
72         NULL
73         NULL
74         NULL

You specify `itemcolumn="UserID"` but you don't have a column UserID in your `queryOfQuery` query — duncan, Jun 29 '16 at 16:09
(EDIT) That would not give us a 500 error, instead we have a server error email sent to us in that case. Even though that might be a problem later, it would be easy to fix. — N. Ziff, Jun 29 '16 at 16:11
definitely, we commented out the 'chartseries' and it still gave us the 500 error. — N. Ziff, Jun 29 '16 at 16:16
You can probably work on the query as my answer suggests but your syntax issue is Percent because it is a reserved keyword and you haven't single quoted it or bracketed it. To find in the future test your sql independent and outside of cold fusion. — Matt, Jun 29 '16 at 16:30
what rdbms. sql-server (if so version?), oracle, mysql???? also is there another table for registrations? because in your sample data the answer for [percent] will always be 1/3 or 33.33% because userid is always unique or do you want the answer as 1 row for all users e.g. 10 registrations / 3 licenses? please provide sample desired result — Matt, Jun 29 '16 at 18:02
Out of that sample data, @Matt, that would be the desired result. — N. Ziff, Jun 29 '16 at 18:22
just for clarity you are saying 10/3 and 1 row or data correct? which database sql-server? — Matt, Jun 29 '16 at 18:28
see bottom of my answer for what I think you want. at this point I have dedicated too much time and you will need to pick up and modify what you want from other sources or open a new question with more details. cheers — Matt, Jun 29 '16 at 18:35

Matt · Answer 1 · 2016-06-29T18:34:28.013

2

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.......

Per your comments this appears to be the query you would want.

SELECT
    COUNT(UserId) AS Registrations
    ,SUM(CASE WHEN StripeCustomerId IS NOT NULL THEN 1 ELSE 0 END) AS Licenses
    ,CASE
       WHEN SUM(CASE WHEN StripeCustomerId IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN 0
       ELSE COUNT(UserId) / SUM(CASE WHEN StripeCustomerId IS NOT NULL THEN 1 ELSE 0 END)
    END AS [Percent]
FROM
    Users
WHERE DATEDIFF(hour, FirstContact, DateStamp) <= '12'

edited Jun 29 '16 at 18:34

answered Jun 29 '16 at 16:24

Matt

13,833
2
16
28

I wanted to get the count of all `non null UserIDs`. The Distinct UserID was there to make sure that the people that bought more than one license did not show up more than once. This query is working well, thanks for the help! – N. Ziff Jun 29 '16 at 16:34
glad it worked for you please accept the answer so others know the question has been answered. thanks. http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Matt Jun 29 '16 at 16:36
Actually, one more thing, the query that you gave me is not dividing correctly, the licenses and percent is the same value that's printed out – N. Ziff Jun 29 '16 at 17:30
1

@N.Ziff I noted in my answer how and why that would happen depending on your data. If you want assistance on the right way to write the query though you will need to provide some sample data and desired result from all tables that should be involved. I think you are probably missing a join to another table but I don't know your data structure. – Matt Jun 29 '16 at 17:40
Oops. I see that now, I will start adding sample data and desired results. – N. Ziff Jun 29 '16 at 17:41
*Percent is a reserved key word* Yes, in some databases, like SQL Server. However, since the question tags do not specify a dbms, you might want to clarify that statement to mention that reserved words can vary by dbms, as does the exact syntax for escaping them. – Leigh Jun 29 '16 at 18:15
as of SQL:2011 Percent is a reserved key word in ansi standard so any rdbms adhering to that ansi standard (oracle, sql-server, postgres) it will be an issue in... so I guess you could say that I have answered according to the ansi standard. but I understand your detailed point. – Matt Jun 29 '16 at 18:27
Well that may be the case in the future, but a lot of databases currently in use, do not yet implement that standard. So stating definitively that it *is* a reserved key word, without knowledge of the dbms in question, is not completely accurate .. but I know what you mean.;-) – Leigh Jun 29 '16 at 18:55

CFChart producing a 500 Error

1 Answers1