SQL Counting Distinct Values From Table With multiple Where possibilities [duplicate]

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: stackoverflow.com/questions/12598120/… — UNOPARATOR
– UNOPARATOR, Commented Oct 5, 2019 at 14:53

Gordon Linoff · Accepted Answer · 2019-10-05 15:09:39Z

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 5, 2019 at 15:09

answered Oct 5, 2019 at 14:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peachman1997 Over a year ago

This return what i need. however the list of SpinePlanID can change depending on the search Exchange?

Peachman1997 Over a year ago

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.

Collectives™ on Stack Overflow

SQL Counting Distinct Values From Table With multiple Where possibilities [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related