Sql-server – How to count more than one time with different conditions

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1

Like this:

WITH multi_design_perfs AS
(SELECT b.perf_id 
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id, 
    STUFF((select ', ' + CAST(b.design AS varchar(10))
     FROM perf_ticket_type b 
     WHERE b.perf_id = m.perf_id
     ORDER BY b.design
     FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;

I think you want to do two different things in one query:

1. randomly choose one name out of all the ones `WHERE g_number=123
2. count all the rows WHERE g_number=123.

If that's what you want to do, do it with two subqueries:

SELECT
   (
     SELECT name
     FROM t
     WHERE g_number = 123
     ORDER BY rand()
     LIMIT 1
   ) AS name
   , (
     SELECT count(*)
     FROM t
     WHERE g_number = 123
   ) AS count_of_123

That will give you as a result:

|    name | count_of_123 |
|---------|--------------|
|    Sara |            3 |

or

|    name | count_of_123 |
|---------|--------------|
| Michael |            3 |

or

|    name | count_of_123 |
|---------|--------------|
|    John |            3 |

You can check it at http://sqlfiddle.com/#!9/5342d2/8/0

This is not just MySQL, it is standard SQL (except, maybe, for the name of the rand() function, that might be DBMS-dependent). The query also reflects clearly the fact that you want to do two (related but different) things. You can check also at SQLFiddle the PostgreSQL version, and the SQLite one.

Within the realm of MySQL, you can use one non-standard query that will also work (and which, performance-wise, is probably better):

SELECT
   name, count(*) AS count_of_123
FROM
   (
     SELECT name
     FROM t
     WHERE g_number = 123
     ORDER BY rand()
   ) AS q0 ;

I think this is closer to what the original poster had in mind. Even so, I wouldn't rely on it. This is very far from standard SQL. MySQL might decide to change how this is implementd and behaves in the future. Also, IMHO, it is less clear what the intent of the query is.

Check it at http://sqlfiddle.com/#!9/5342d2/10/0