I am trying to build a set of arrays from a php script for use in Google Chart, and I'm having a little trouble making this work as a stacked column graph. I've been trying to make the query work just by running commands in MySQL Workbench, but I'm having some issues.

What I want the script to do is list each distinct Category, and a series of fields that are a count of the subcats for each category (even if it's 0).

For example, my table looks like this, with somedata showing that there's more in the table:

I'm trying to get my query to give me the following data:

Then format it for JavaScript so it appears like this within data (code snippet below):

var data = google.visualization.arrayToDataTable([

        //list subcategories for chart Legend, append role
        ['Soft', 'Hard', 'Cold', 'Hot', 'Sharp', 'Dull', 'Rough', 'Smooth', { role: 'annotation' } ],

        //formatted arrays for stacked columns 
        ['Red', 1, 2, 0, 0, 0, 0, 0, 0, ''],
        ['Blue', 0, 0, 1, 1, 0, 0, 0, 0, ''],
        ['Green', 0, 0, 0, 0, 1, 1, 0, 0, ''],
        ['Yellow', 0, 0, 0, 0, 0, 0, 1, 2, '']

   ]);

I've been testing simple queries to see how far I can get. So far, it's resulted in the following:

I haven't even gotten to the php portion of this. Any help would be greatly appreciated.

SELECT DISTINCT Category, (
SELECT COUNT (Subcat) 
FROM tbl_table 
WHERE Subcat = 'Soft' AND Category = 'Blue'
) 
AS 'Soft', 
#the above portion repeats for each category/subcat combination that's valid
From tbl_table

I've tried a few other queries, but didn't have them handy to paste here.

CodePudding user response：

Here I use an alias of t1 to refer to the subquery’s table. Then you can limit the sub query to the same category as the main query.

SELECT DISTINCT Category, (
SELECT COUNT (Subcat) 
FROM tbl_table t1
WHERE Subcat = 'Soft' AND t1.Category = tbl_table.Category
) 
AS 'Soft', 
#the above portion repeats for each category/subcat combination that's valid
From tbl_table