phpMyAdmin - not reading MySQL query as numeric

Question

I am trying the graph query results in phpMyAdmin. The query below is correctly calculating the median and 25% and 75% quartiles. However, when I click on "Display Chart" in the Query Results Operations on the buttom of the page, it gives me an error message that reads "no numeric columns present in the data to plot." However, if I write avg(capital_cost) that will graph with out any issues. So for some reason mySQL is not recognizing each of the quartile numbers as numeric.

In the structure of my database, the "capital_cost" field is set as type "decimal(19,2)"

  select distinct Component, 

   CONCAT('$', format(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
  GROUP_CONCAT(capital_cost ORDER BY capital_cost SEPARATOR ','),
   ',', 25/100 * COUNT(*) ), ',', -1) as decimal),2)) AS 'Q1',

 CONCAT('$', format(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
  GROUP_CONCAT(capital_cost ORDER BY capital_cost SEPARATOR ','),
   ',', 50/100 * COUNT(*) ), ',', -1) as decimal),2)) AS 'Q2',

 CONCAT('$', format(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
  GROUP_CONCAT(capital_cost ORDER BY capital_cost SEPARATOR ','),
   ',', 75/100 * COUNT(*) ), ',', -1) as decimal),2)) AS 'Q3',


from costs_table where Component = "X" and capital_cost is not null

If anyone knows any additional functions I can add to have MYSQL accept these values as numbers and correctly graph them, please let me know!

Thank you in advance :)

I believe you can find help in this thread: stackoverflow.com/questions/11830509/cast-to-decimal-in-mysql — andershagbard
– andershagbard, Commented Jul 10, 2014 at 19:35
I have already applied the cast as decimal function, which is why I am confused that it is not working. — user3760644
– user3760644, Commented Jul 10, 2014 at 19:43

VMai · Accepted Answer · 2014-07-10 22:22:49Z

The answer is very easy. You do a lot to get a string and not a DECIMAL: How do you expect that your CONCAT operation with the "$" sign returns anything else than a string? Omit it, simplify your query to:

CAST(
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            GROUP_CONCAT(
                capital_cost ORDER BY capital_cost SEPARATOR ','
            ),
            ',', 25/100 * COUNT(*) 
        ), ',', -1
    ) as decimal
) AS 'Q1',

CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
   GROUP_CONCAT(capital_cost ORDER BY capital_cost SEPARATOR ','),
   ',', 50/100 * COUNT(*) ), ',', -1) as decimal) AS 'Q2',

CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
    GROUP_CONCAT(capital_cost ORDER BY capital_cost SEPARATOR ','),
    ',', 75/100 * COUNT(*) ), ',', -1) as decimal) AS 'Q3'

FROM
    costs_table 
WHERE 
     Component = "X" and capital_cost is not null

and you should be fine.

Note:

I reformatted your first expression mainly to get sure I got the brackets right.

Collectives™ on Stack Overflow

phpMyAdmin - not reading MySQL query as numeric

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related