Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

count of a value from different column by column wise count

J
jules last edited by

I have a table with columns having values 0 and 1 like give below

Column A = 0,1,0 Column B = 0,1,1 Column C = 1,1,1

I need to count occurrence of "1" from each column. (eg. A = 1, B = 2, C = 3). Kindly guide me to get the count based on column count.
Reply Quote 0
1 Reply Last reply
C
chanisef last edited by
Let's use a string trick. Try this:
```
SELECT LENGTH("0,1,1") - LENGTH(REPLACE("0,1,1", "1", ""));
```
It returns 2.

So:
```
SELECT LENGTH(A) - LENGTH(REPLACE(A, "1", "")) AS A_count,
       LENGTH(B) - LENGTH(REPLACE(B, "1", "")) AS B_count,
       LENGTH(C) - LENGTH(REPLACE(C, "1", "")) AS C_count
    FROM ...
```
If you had a bit string, see BIT_COUNT(). If you have a character string with 0s and 1s, BIT_COUNT(convert(my_str, 2, 10));

Consider writing a Stored Function to encapsulate the tricky code.
Reply Quote 0
1 Reply Last reply

mysql 49766

2
Posts

0
Views

Suggested Topics

K

optimizing MySQL for traffic analytics system
SQL, Database Testing • mysql mongodb index sc languagealability memc languageac languageh • • kalyn

2

0
Votes

2
Posts

0
Views

N

You can simplify your query to something along these lines. I expect MySQL will generate a simpler execution plan. SELECT date(date) period, count(*) clicks FROM visits WHERE url_id = 3 AND DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH ) GROUP BY period; If returning results on high-traffic links in one second is a hard requirement, you might need to upgrade your hardware. Expect to need an index on each column used in a WHERE clause. You might benefit from some multicolumn indexes; {url_id, date} is a candidate. Test at scale, if that's at all possible. (It's usually possible, although it might take some time.) Use EXPLAIN to see what MySQL is doing with your queries. You don't have to query browsers, countries, and everything else all at one time. When I was running web development, I rarely looked at countries--they weren't relevant to the niche I was working in. Also consider other asynchronous UI technologies. PostgreSQL has a better optimizer. It might work better than MySQL. Test it.
"Merging" grouped rows (column-wise, picking the first non-null value, ordered by another column)
SQL, Database Testing • group by orac languagele 11g • • Analeea

2

0
Votes

2
Posts

0
Views

E

You can use the FIRST_VALUE analytic function for this: select distinct record_group, first_value(col_a ignore nulls) over (partition by record_group order by record_level rows between unbounded preceding and unbounded following) as col_a, first_value(col_b ignore nulls) over (partition by record_group order by record_level rows between unbounded preceding and unbounded following) as col_b, first_value(col_c ignore nulls) over (partition by record_group order by record_level rows between unbounded preceding and unbounded following) as col_c from tbl_record order by record_group ; RECORD_GROUP COL_A COL_B COL_C -------------------- -------------------- ---------- -------------------- group1 foo2 0 bar1 group2 foo3 5 bar4 group3 foo6 1 We find the first value (FIRST_VALUE) for each row, ignore nulls (ignore nulls), for the current record_group "partition/group" (partition by record_group), for the whole "partition/group" (rows between unbounded preceding and unbounded following) based on the order of record_level (order by record_level), and get rid of the duplicate rows with DISTINCT.
Storing huge amount of user game data
SQL, Database Testing • mysql database design • • Demir

2

0
Votes

2
Posts

0
Views

R

First of all, you need to stop thinking in matrices. You have 2 options. 1) Store each game & who won. 2) Store aggregates of who won. Option 1 Store every game and the result of said game. Also allows more data about each game to be recorded within the games row. create table players ( player_id integer, player_username varchar(50), other_player_related_column datatype, ... ... ... ); create table games ( player1_id integer, -- references players table player2_id integer, -- references players table winner_id integer, -- the player_id of the winner interesting_game_fact datatype -- other game info ); Option 2 Just store aggregates of the results of games played between two players. Losses can be derived from the number of games the opponent won. There should only be 1 row for each player_id pair - I suggest storing the lower of the two player_ids as player1_id, just to make life easier. create table players ( player_id integer, player_username varchar(50), other_player_related_column datatype, ... ... ... ); create table results ( player1_id integer, -- references players table player2_id integer, -- references players table player1_wins integer, -- number of times player 1 has won player2_wins integer -- number of times player 2 has won );
E

Which method I use to insert a data form into array in php and mysql
Software Programming • php mysql • • emerson

2

0
Votes

2
Posts

0
Views

R

The problem is that in your array the data comes from parts, then it's not that simple align them so that the query assumes each value of each column of each sub-array in a row.Apart from that, we are facing an uneven array because the usuario He's going to his air.Through this code you can organize the array to then dynamize the insertion. However, it would be useful for you to plan (if you can) otherwise organize the code in the source that sends those data, so that it sends them to you with a structure that is consistent with what you need.This is the code I propose. I'm not going to work to insert it into your context, I think you can do without much difficulty. It's a good idea to put it in a context of transactions, as seen in your attempt.I just leave you the basics, which is to treat $_POST, to organize the data and then assemble the preparation of the query and its binding with the data.I've put a literal query to prove it should work, if you incorporate it well.#Para sacar los datos del usuario aparte $usuario=array(); #Quitamos los datos del usario para poder combinar los otros datos foreach ($_POST as $k=>$v){ if (!is_array($v)){ unset ($_POST[$k]); $usuario[$k]=$v; } } #Aquí combinamos los datos de cada array particular #para armar una fila completa de valores $rows=array_map(function(){return func_get_args();},...array_values($_POST)); //echo implode(",",$usuario); $types="sssssss"; foreach ($rows as $row){ #Combinamos los datos del usuario, con los de cada fila $params=array_merge($usuario,$row); #Sólo para probar la consulta... borrar $forTest="INSERT INTO spesa (usuario, fecha, codice, articolo, quantita, stato, note) VALUES (".implode(',',$params).")"; echo $forTest.PHP_EOL; #Consulta preparada $sql="INSERT INTO spesa (usuario, fecha, codice, articolo, quantita, stato, note) VALUES (?,?,?,?,?,?,?)"; #Desde la versión 5.6+ PHP permite usar ...$params #en versiones anteriores hay que recurrir a call_user_func $stmt = $con->prepare($sql); $stmt->bind_param($types, ...$params); if(!$stmt->excute()){ #Sólo para debug, no dejes esta línea con mensajes internos echo $con->error; } } ObservationIf the column fecha It's the guy. DATE or DATETIME, you must convert the value of fecha in the $_POST to a format YYYY/MM/DD. That you can do in the part where you assign $usuarios and is made unset.The output of the test variable $forTest is this:INSERT INTO spesa (usuario, fecha, codice, articolo, quantita, stato, note) VALUES (Valter Lafratta,16/03/2020,302,Cacio Molisano Bianco Stagionato Kg. 1,5,1,Confermata,primer producto) INSERT INTO spesa (usuario, fecha, codice, articolo, quantita, stato, note) VALUES (Valter Lafratta,16/03/2020,245,Cacio rettangolare gr 1500,2,Confermata,segundo producto) INSERT INTO spesa (usuario, fecha, codice, articolo, quantita, stato, note) VALUES (Valter Lafratta,16/03/2020,624,Fusilli Mediterranei 500 gr,3,Confermata,tercer producto) INSERT INTO spesa (usuario, fecha, codice, articolo, quantita, stato, note) VALUES (Valter Lafratta,16/03/2020,21,LATTE F.P.S. DA 1/2 LT PET,4,Confermata,cuarto producto)
O

Count the number of occurrences of certain values of TagName column
SQL, Database Testing • sql server count • • Oba22

2

0
Votes

2
Posts

0
Views

R

Sounds like a simple group by: select tagname, count(*) from alarmmaster where tagname like '%Sensor%' or groupname like '%Sensor%' group by tagname;
Count where two or more columns in a row are over a certain value [basketball, double double, triple double]
SQL, Database Testing • mysql postgresql group by count • • carriann

2

0
Votes

2
Posts

0
Views

Don't know if this is the best way. I first did a select to find out if a stat is double digit and assign it a 1 if it is. Summed all those up to find out total number of double digits per game. From there just sum up all the doubles and triples. Seems to work select a.player_id, a.team, sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles, sum(case when a.doubles = 3 then 1 else 0 end) as tripleDoubles from (select *, (case when points > 9 then 1 else 0 end) + (case when rebounds > 9 then 1 else 0 end) + (case when assists > 9 then 1 else 0 end) + (case when steals > 9 then 1 else 0 end) + (case when blocks > 9 then 1 else 0 end) as Doubles from PlayerGameStats) a group by a.player_id, a.team
J

Failure to connect the database with redbeanphp
Software Programming • php mysql redbeanphp • • juvenalb

2

0
Votes

2
Posts

0
Views

I will use this issue to draw the attention of all the newcomers to two very important points:The starter very important. read the error report carefully. Even with a google translator, it will give at least some insight into the problem. In particular, it has nothing to do with the database list. It's about the file we're trying to connect through warrants. I mean, after reading the erroneous report, you're not going to mess with the injured sigak on the other side, but you're gonna be dealing with the specific cause of the problem. In particular, it is very helpful to diagnose the error.It is very important for the starter to examine at least the basis of computer literacy. In this case, http://phpfaq.ru/newbie/paths ♪ In this case, it is clear that an attempt is under way to connect the file. /lib/rb.php from the root of the disc, although, of course, there is no such file. The file is down. at the root of the siteI mean, you need to treat him properly.require $_SERVER['DOCUMENT_ROOT'].'/lib/rb.php';
SQL Server - Get a new columns with name of the columns have the values X base on rows
SQL, Database Testing • sql server • • Alberto

2

0
Votes

2
Posts

0
Views

J

If you don't know the table structure, one possible approach is to generate and execute a dynamic statement. Use the information from sys.columns and sys.tables views to generate the statement. Table: CREATE TABLE Data ( id int, field_1 varchar(1), field_2 varchar(1), field_3 varchar(1), field_4 varchar(1), field_5 varchar(1) ) INSERT INTO Data (id, field_1, field_2, field_3, field_4, field_5) VALUES (1, 'X', NULL, 'X', NULL, 'X'), (2, NULL, 'X', 'X', NULL, NULL), (3, NULL, NULL, NULL, 'X', NULL) Statement: -- Declaration DECLARE @stm nvarchar(max) = N'' -- Columns names SELECT @stm = CONCAT(@stm, ', CASE WHEN ', col.[name], ' = ''X'' THEN ''', col.[name], '-'' ELSE '''' END') FROM sys.columns col JOIN sys.tables tab ON col.object_id = tab.object_id WHERE (tab.[name] = 'Data') AND (col.[name] LIKE 'field_%') -- While statement SET @stm = CONCAT( N'SELECT *, CONCAT(', STUFF(@stm, 1, 1, N''), N') AS NewColumn FROM Data' ) -- Execution PRINT @stm EXEC sp_executesql @stm Output: ------------------------------------------------------------------- id field_1 field_2 field_3 field_4 field_5 NewColumn 1 X NULL X NULL X field_1-field_3-field_5- 2 NULL X X NULL NULL field_2-field_3- 3 NULL NULL NULL X NULL field_4-
F

If you try to access MySQL, the connection error cannot be accessed.
Software Programming • mysql • • fbio

2

0
Votes

2
Posts

0
Views

According to error messagesmysqldHomec:\00_myspace\01_server\mysqlLet's make a file.@ToshimizuD:\myplace\01_server\mysqlSince the file is placed inQiita [mysqld] # set basedir to your installation path basedir=C:\00_myspace\01_server\mysql\ # set datadir to the location of your data directory datadir=C:\00_myspace\01_server\mysql\data Home[mysqld] # set basedir to your installation path basedir=D:\myspace\01_server\mysql\ # set datadir to the location of your data directory datadir=D:\myspace\01_server\mysql\data Please rewrite it.
M

How do I include zeros in a count(column)?
SQL, Database Testing • orac languagele 11g • • meriah

2

0
Votes

2
Posts

0
Views

T

There's not much information you provide, so this is only a guess. Your WHERE clause Tab2.Col1 = Tab1.Col1(+) may be backward, with regard to the (+) ... I would recommend switching it to a LEFT JOIN regardless, just to make it more readable.. select count(b.Col1) "Number of Records", --- CHANGED a.Col1 to b.Col1 a.col2 "Type", to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total" from Tab1 a left join Tab2 b on tab1.col1 = tab2.col1 -- or (from tab2 left join tab1) if that is what's intended group by a.Col1; If you are still not getting the 0 counts, then you should try a coalesce statement. select COALESCE(count(b.Col1), 0) AS "Number of Records", --- COALESCE HERE .. CHANGED a.Col1 to b.Col1 a.col2 "Type", to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total" from Tab1 a left join Tab2 b on tab1.col1 = tab2.col1 group by a.Col1; You are not referencing table b anywhere in your sql, though - please explain why you need to join to it if you're not going to be using its data?
Distinct in one column where another column has same values
SQL, Database Testing • sql server distinc languaget • • briley

2

0
Votes

2
Posts

0
Views

J

SELECT name, MIN(type) AS type FROM table_name GROUP BY name HAVING COUNT(DISTINCT type) = 1 ; or the same with: HAVING MIN(type) = MAX(type) I assume that type is not nullable. If you want only those that have type = 'a', use: HAVING MIN(type) = 'a' AND MAX(type) = 'a' ;
Query to get counts of values per column
SQL, Database Testing • postgresql count postgresql 9.6 • • Marcee

2

0
Votes

2
Posts

0
Views

G

You can get the first part of that done easily like this, SELECT FORMAT( E'SELECT %s\nFROM %I.%I.%I;' -- query template , string_agg( -- generate the select list for query template FORMAT('count(DISTINCT %I) AS %I', column_name, column_name) , E',\n\t' ), table_catalog, -- not strictly required, but future safe table_schema, table_name ) FROM information_schema.columns WHERE table_name = 'foo' GROUP BY table_catalog, table_schema, table_name; This will return a query like this, SELECT count(DISTINCT column1) AS column1, count(DISTINCT column2) AS column2, count(DISTINCT column3) AS column3, count(DISTINCT column4) AS column4 FROM ecarroll.public.foo; Which is pretty much what you want, except you need to pivot it. column1 | column2 | column3 | column4 ---------+---------+---------+--------- 2 | 3 | 3 | 0 To do that pivot, we can use unnest(ARRAY[cols]) AS col_name, so we essentially have to generate dynamic SQL to do the count() wrap that with more dynamic sql to do the pivot. Like this, SELECT FORMAT( $$ SELECT ordinality AS column_number, distinct_values -- the col#, and count FROM ( SELECT %s -- This was the query we FROM %I.%I.%I -- used previously ) AS t CROSS JOIN unnest(ARRAY[%s]) WITH ORDINALITY -- Here we use unnest(array) AS distinct_values; -- to pivot the table $$, string_agg( FORMAT('count(DISTINCT %I) AS %I', column_name, column_name) , E',\n\t' ), table_catalog, table_schema, table_name, string_agg(column_name, ', ') ) FROM information_schema.columns WHERE table_name = 'foo' GROUP BY table_catalog, table_schema, table_name; That returns a query like this.. SELECT ordinality AS column_number, distinct_values FROM ( SELECT count(DISTINCT column1) AS column1, count(DISTINCT column2) AS column2, count(DISTINCT column3) AS column3, count(DISTINCT column4) AS column4 FROM ecarroll.public.foo ) AS t CROSS JOIN unnest(ARRAY[column1, column2, column3, column4]) WITH ORDINALITY AS distinct_values; And you can just run \gexec and you'll get, column_number | distinct_values ---------------+----------------- 1 | 2 2 | 3 3 | 3 4 | 0
K

Comments for each id post without multiplication of the same
Software Programming • php mysql mysqli • • kalena

2

0
Votes

2
Posts

0
Views

C

// VARIAVEL DE CONTROLE PARA TROCA DE QUESTÃO. $idControle = 0; while($row = $result->fetch_assoc()) { // A QUESTÃO ATUAL É DIFERENTE DA QUESTÃO ANTERIOR? SE FOR IMPRIME CABEÇALHO. if($idControle != $row['question_ID']){ echo "<hr> TITLE: " . $row["titulo"] . " QUESTION: " . $row["question"]." "; } echo " COMMENT: " . $row["comment"] . " "; // ATUALIZA VARIAVEL DE CONTROLE. $idControle = $row['question_ID']; }
C

How do you speed up the request to base?
Software Programming • php mysql phpmyadmin • • charlenef

2

0
Votes

2
Posts

0
Views

J

We've got to make an index group. Readed. All lines are poorly indexed by B+ trees, by definition.SELECT * FROM ( SELECT U.id as user_id, count(O.id) as total FROM users AS U LEFT JOIN users_to_orders AS UO ON U.id = UO.id_user LEFT JOIN orders AS O ON UO.id_orders = O.id GROUP BY U.id DESC ) AS P1 INNER JOIN users AS P2 ON P1.user_id = P2.id ORDER BY P1.total DESC LIMIT 0, 500;We've got to do the hushing. You need 500 results - update them once. N One second. For example, the time table aka materialized view (yes, they can be emulated on a trigger).InnoDb. It's easy.
S

Percentage difference of the last value from the previous values of a column based on certain data group within the same table
SQL, Database Testing • t sql • • simrah

2

0
Votes

2
Posts

0
Views

Y

If your version of SQL Server is recent enough (2012+), the problem can be solved with the window/analytic function LAST_VALUE(): Column4 = (LAST_VALUE(Column3) OVER (PARTITION BY Column2 ORDER BY Column1) - Column3) / Column3 * 100 or FIRST_VAUE() with inverse order: Column4 = (FIRST_VALUE(Column3) OVER (PARTITION BY Column2 ORDER BY Column1 DESC) - Column3) / Column3 * 100

count of a value from different column by column wise count

Suggested Topics

optimizing MySQL for traffic analytics system SQL, Database Testing • mysql mongodb index sc languagealability memc languageac languageh • • kalyn

"Merging" grouped rows (column-wise, picking the first non-null value, ordered by another column) SQL, Database Testing • group by orac languagele 11g • • Analeea

Storing huge amount of user game data SQL, Database Testing • mysql database design • • Demir

Which method I use to insert a data form into array in php and mysql Software Programming • php mysql • • emerson

Count the number of occurrences of certain values of TagName column SQL, Database Testing • sql server count • • Oba22

Count where two or more columns in a row are over a certain value [basketball, double double, triple double] SQL, Database Testing • mysql postgresql group by count • • carriann

Failure to connect the database with redbeanphp Software Programming • php mysql redbeanphp • • juvenalb

SQL Server - Get a new columns with name of the columns have the values X base on rows SQL, Database Testing • sql server • • Alberto

If you try to access MySQL, the connection error cannot be accessed. Software Programming • mysql • • fbio

How do I include zeros in a count(column)? SQL, Database Testing • orac languagele 11g • • meriah

Distinct in one column where another column has same values SQL, Database Testing • sql server distinc languaget • • briley

Query to get counts of values per column SQL, Database Testing • postgresql count postgresql 9.6 • • Marcee

Comments for each id post without multiplication of the same Software Programming • php mysql mysqli • • kalena

How do you speed up the request to base? Software Programming • php mysql phpmyadmin • • charlenef

Percentage difference of the last value from the previous values of a column based on certain data group within the same table SQL, Database Testing • t sql • • simrah

optimizing MySQL for traffic analytics system
SQL, Database Testing • mysql mongodb index sc languagealability memc languageac languageh • • kalyn

"Merging" grouped rows (column-wise, picking the first non-null value, ordered by another column)
SQL, Database Testing • group by orac languagele 11g • • Analeea

Storing huge amount of user game data
SQL, Database Testing • mysql database design • • Demir

Which method I use to insert a data form into array in php and mysql
Software Programming • php mysql • • emerson

Count the number of occurrences of certain values of TagName column
SQL, Database Testing • sql server count • • Oba22

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]
SQL, Database Testing • mysql postgresql group by count • • carriann

Failure to connect the database with redbeanphp
Software Programming • php mysql redbeanphp • • juvenalb

SQL Server - Get a new columns with name of the columns have the values X base on rows
SQL, Database Testing • sql server • • Alberto

If you try to access MySQL, the connection error cannot be accessed.
Software Programming • mysql • • fbio

How do I include zeros in a count(column)?
SQL, Database Testing • orac languagele 11g • • meriah

Distinct in one column where another column has same values
SQL, Database Testing • sql server distinc languaget • • briley

Query to get counts of values per column
SQL, Database Testing • postgresql count postgresql 9.6 • • Marcee

Comments for each id post without multiplication of the same
Software Programming • php mysql mysqli • • kalena

How do you speed up the request to base?
Software Programming • php mysql phpmyadmin • • charlenef

Percentage difference of the last value from the previous values of a column based on certain data group within the same table
SQL, Database Testing • t sql • • simrah