SQL Counting Distinct Values From Table With multiple Where possibilities

Question

The question is simple. How do i combine the following 2 sql queries into one.

I am using MYSQL v8.0.16 on the MYSQL Workbase UI.

SELECT Node_Type, COUNT(*)  AS `count`
FROM node_table_vis 
WHERE Node_SpinePlanID = "1"
GROUP BY Node_Type;

RETURNS

TYPE - COUNT
-----------
AGN     18
TJ      26
DSLAM   15
PON     18
CCJ     17

and

SELECT DISTINCT Node_SpinePlanID
FROM node_table_vis
WHERE Node_Exchange = "Exchange 1";

Thats returns

SpinePlanID
------------
1
5
10

So essentially what i want is query that looks like this?

SELECT Node_Type, COUNT(*)  AS `count`
FROM node_table_vis 
WHERE Node_SpinePlanID =
(
    SELECT DISTINCT Node_SpinePlanID
    FROM node_table_vis
    WHERE Node_Exchange = "Exchange 1";
)
GROUP BY Node_Type;

So I get table that looks like

TYPE -  1  -  5  -  10
-----------------------
AGN     18    x     y
TJ      26    x     y
DSLAM   15    x     y
PON     18    x     y
CCJ     17    x     y

So this is just throwing errors and not producing the goods. I was able to find the answer the top query, i was able to make the bottom, however i am unable to find an answer to combine both.

Any advice would be really appreciated.

UPDATE/EDIT

I have the following ...

SET @@group_concat_max_len = 10000;
SET @sql = null;
SELECT group_concat(distinct
    concat(
        'SUM(Node_SpinePlanID = ''',
        Node_SpinePlanID,
        ''',) AS ',
        Node_SpinePlanID
    )
) INTO @sql 
FROM node_table_vis;

SET @sql = CONCAT('SELECT Node_Type, ', @sql, ' FROM node_table_vis GROUP BY Node_Type');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

However the PREPARE stmt is not working? Getting error Code 1064 You have an SQL Error Syntax?

Any Advice on this one?

If the columns would change dynamically, you should look at this post: https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns — UNOPARATOR, Oct 05 '19 at 14:53

Gordon Linoff · Answer 1 · 2019-10-05T15:09:39.833

1

Are you looking for conditional aggregation?

SELECT Node_Type,
       SUM(Node_SpinePlanID = 1) AS count_1,
       SUM(Node_SpinePlanID = 5) AS count_5,
       SUM(Node_SpinePlanID = 10) AS count_10
FROM node_table_vis 
GROUP BY Node_Type;

You can also put the values into rows:

SELECT Node_Type, Node_SpinePlanID, COUNT(*) as cnt
FROM node_table_vis 
GROUP BY Node_Type, Node_SpinePlanID;

Without dynamic SQL, probably the best you can do is to put the values in a single row is to put them in a string with two levels of aggregation:

SELECT Node_Type, GROUP_CONCAT(Node_SpinePlanID, ':', cnt SEPARATOR ',')
FROM (SELECT Node_Type, Node_SpinePlanID, COUNT(*) as cnt
      FROM node_table_vis 
      GROUP BY Node_Type, Node_SpinePlanID
     ) ts
GROUP BY Node_Type;

edited Oct 05 '19 at 15:09

answered Oct 05 '19 at 14:52

Gordon Linoff

1,242,037
58
646
786

This return what i need. however the list of SpinePlanID can change depending on the search Exchange? – Peachman1997 Oct 05 '19 at 14:54
I could work with the query. as it gives me the results and I can filter by the exchange so i can make that work. if you see my edit, someone suggested prepared statements? I think I'm close however i minor issue if you see my edit above. – Peachman1997 Oct 05 '19 at 15:32

SQL Counting Distinct Values From Table With multiple Where possibilities

1 Answers1