Count – How to Count Multiple Occurrences of Attribute Values for Each Row in SQL

count

I have a mytable structured as follows and I would like to count occurences of values for attribute in each row:

id | attribute
--------------
1  | spam
2  | egg
3  | spam

With

SELECT id, attribute, COUNT(attribute) FROM mytable GROUP BY attribute

I only get

id | attribute | count
----------------------
1  | spam      | 2 
2  | egg       | 1

But what I would like as a result is

id | attribute | count
----------------------
1  | spam      | 2 
2  | egg       | 1
3  | spam      | 2

How to achieve this?

Best Answer

select
  m1.id, 
  m1.attribute, 
  (select count(*) from mytable m2 where m2.attribute = m1.attribute) 
from
  mytable m1
;

Another version:

select
  m1.id,
  m1.attribute,
  m2.c
from
  mytable m1
  join (SELECT attribute, COUNT(attribute) as c FROM mytable GROUP BY attribute) m2
  on (m1.attribute = m2.attribute)
;

A better version for databases with analytic/window functions:

select
  m1.id,
  m1.attribute,
  count(*) over (partition by m1.attribute)
from
  mytable m1
;

Related Solutions

Mysql – Help with COUNT across multiple columns

Given the new information (you should probably edit the question, not add an answer, but I assume you are not entitled to). First I don't understand the database design, do you always get 4 affected devices per fault? Anyhow, you can transpose your table like:

select affected_device_1 from faults
union all
select affected_device_2 from faults
union all
select affected_device_3 from faults
union all
select affected_device_4 from faults

I won't be efficient but it is the best I can think of. From there you can count the number of occurrences for each device:

select affected_device, count(1)
from (
    select affected_device_1 as affected_device from faults
    union all
    select affected_device_2 as affected_device from faults
    union all
    select affected_device_3 as affected_device from faults
    union all
    select affected_device_4 as affected_device from faults
) as T
group by affected_device;

Note that if a fault contains the same device twice the count will be wrong.

Mysql – count null values of table from information_schema tables

No, you cannot use information schema for this. Information_schema.TABLES only stores an (approximate) count of the number of rows in a table. Not counts of values or nulls.

You should have an index on the column and use the simple query instead:

-- if there isn't an index in place, add one:
alter table my_table
  add index (my_column_idx)
    (my_column) ;

select count(*)
from my_table
where my_column is null ;

But can i add an index on a column if column_type = mediumtext?

Well, no.

MySQL version 5.1.73 and table is MyISAM.

OK, now we have 3 problems.

First, you are in a very old, unsupported version of MySQL. Upgrade. At least to 5.5, if not to 5.6 or 5.7. This is not urgent of course and only partially related to the problem. Even at 5.7, we still can't have an index on a mediumtext column.

Second, you are using MyISAM? Still, in 2016? I guess it matches well with 5.1. Anyway, convert the tables and application to use InnoDB. That's not urgent or related to the issue either but you'll get benefits (transactions, foreign keys, etc). But no, even with InnoDB, we still can't have an index on a mediumtext column.

So, what to do? various ways - thrown not in any specific order:

Add a TINYINT computed (GENERATED) column on something like CASE WHEN column IS NOT NULL THEN 1 END and index it. Requires upgrade to 5.7
Add a TINYINT computed (VIRTUAL) column, as the previous suggestions, and index it. Requires replacing MySQL 5.1 with MariaDB 5.2 or 5.3 (or upgrading to 5.5 and then replacing with MariaDB 5.5 or 10+)
Add a TINYINT column (like the computed ones) but fill the values and keep them correct by your application or triggers. This does not require upgrading but enforcing integrity in the application is not recommended. Considering the use of MyISAM though, integrity does not seems a priority.
Banish the Nulls! Split the table into two, moving only the mediumtext column into the 2nd table (and the primary key). The 2nd table will only have rows for the non-null values of the column. Then you only need to count rows of that table. You can use the information schema for that! (or just count, MyISAM is fast in counting the whole table.) Does not require any upgrade but your applications will have to change to deal with the new, slightly altered table structure.
use a prefix index (thnx to @Michael-sqlbot for the suggestion). This does not require any upgrade or modifications at all:
```
-- a prefix index on the mediumtext column:
alter table my_table
  add index (my_column_1_idx)
    (my_column(1)) ;      -- one is enough
```
I haven't tested this but should work as it is. It may require to use WHERE LEFT(my_column, 1) IS NULL in your count query but you can test.

Best Answer

Related Solutions

Mysql – Help with COUNT across multiple columns

Mysql – count null values of table from information_schema tables

Related Question