sql query not working on table creation, but works on phpmyadmin

  • Resolved brunocerq

    (@brunocerq)


    4 years, 8 months ago

    Hi,
    I’m trying to create a new wpdatatable from a SQL query, I tested the query on phpmyadmin and it generates the desired results, but I get an error message on WPDatatables trying to make that query.

    

WITH
    top10 AS(
    SELECT
        name,
        market_cap
    FROM
        coindata
    ORDER BY
        market_cap
    DESC
LIMIT 10
)
SELECT
    *
FROM
    top10
UNION ALL
SELECT
    'All other' AS NAME,
    SUM(market_cap) AS market_cap
FROM
    coindata
WHERE
    name NOT IN(
SELECT
    name
FROM
    top10
)

    Query explanation: selects the Top 10 items by market_cap and ads a column in the bottom as “All Other” with the sum of all other market_cap that are not in the top10.

    • This topic was modified 4 years, 8 months ago by brunocerq.
Viewing 1 replies (of 1 total)
  • Plugin Author wpDataTables

    (@wpdatatables)

    Hi there,

    Please note that this feature is not available in Lite version of plugin. We will answer you now, but for further questions or issues please open new ticket on our support platform and our support agents will help you out.

    If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.

Viewing 1 replies (of 1 total)
  • The topic ‘sql query not working on table creation, but works on phpmyadmin’ is closed to new replies.