SQL attempting to group by multiple columns with "chained" column generation

Question

I'm trying to get SQL to generate a count of rows based on 2 values, from a table like shown below

Table Data:

ID	NAME	VALUE	device_id
1	name_1	enabled	1
2	name_2	enabled	2
3	name_3	enabled	3
4	name_1	disabled	1
5	name_2	disabled	4
6	name_3	disabled	6
1	name_1_ts		1
2	name_2_ts		2
3	name_3_ts		5
.
.
.
99999	name_1	enabled	8329
100000	name_2	disabled	5

Return data that I want to generate

count(*)	name	value
500	name_1	enabled
1000	name_2	enabled
3500	name_3	enabled
1500	name_1	disabled
2000	name_2	disabled
1500	name_3	disabled

I'm trying

select count(*), name, value 
from TABLE 
where value = 'enabled' or value = 'disabled' 
group by name, value;

But it's not generating the results I'm looking for. What am I doing wrong here, and what I can I do fix it?

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented Nov 12 at 18:10
I removed conflicting tags, please specify if you are using mysql or mariadb — Charlieface
– Charlieface, Commented Nov 12 at 18:31
But it's not generating the results I'm looking for. Please include the results that you are looking for. We're not psychic. — MatBailie
– MatBailie, Commented Nov 12 at 21:28

Dale K · Accepted Answer · 2025-11-13 03:56:09Z

1

It looks like you want the enableds first, then the disableds. To get that you order by the values, then the names. The values you need in descending order, because enabled comes before disabled in lexical order.

Start with this:

select count(*),name, value
  from TABLE
 group by value, name
 order by value desc, name;

Then, maybe you can filter by the values if you need to.

select count(*),name, value
  from TABLE
 where value = 'enabled' or value = 'disabled'
 group by value, name
 order by value desc, name;

edited Nov 13 at 3:56

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Nov 12 at 18:27

O. Jones

110k17 gold badges134 silver badges187 bronze badges

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

2 Comments

Ergest Basha Nov 13 at 8:30

Out of curiosity, why using where value = 'enabled' or value = 'disabled' and not where value in ('enabled','disabled') ?

O. Jones Nov 14 at 14:34

IN(list, of, items) sometimes is handled suboptimally by the query planner. I've been bitten by it often enough that I avoid it. Of course, OR is also sometime handled suboptimally.

Dale K · Accepted Answer · 2025-11-13 03:54:19Z

0

O. Jones

Thanks for the help, this fixed my issue. The query I ended up using was

select count(*), name, value
from TABLE
where name not like '%ts%'
group by value, name
order by name;

And this did exactly what I needed.

edited Nov 13 at 3:54

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Nov 12 at 19:14

Anurag Kumar

217 bronze badges

1 Comment

Dale K Nov 13 at 3:55

How does name not like '%ts%' come into it? Nothing in your questions mentions this.

Collectives™ on Stack Overflow

SQL attempting to group by multiple columns with "chained" column generation

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related