30068

how to count horizontal values on a database?

Question:

assuming that I have a db that have an horizontal structure like this:

ID | NAME | DATA1 | DATA2 | DATA3 | DATA4 | DATA5 | DATA6 | DATA7 1 | mmm | 0 | 1 | 0 | 3 | 5 | 1 | 0 2 | bbb | 0 | 0 | 0 | 1 | 0 | 1 | 1

the informations are the data fields and I would like to count all the times that a certain discriminant, such as "is more than 0"

the way I thought it is loop trought all the fields, and count, or COUNT() each DATA field, so SUM() those 7 queries... anyone has another idea?

in this case the result, so "count every DATA field with a value over 0" would be = 7

Answer1:

There's no built-in syntax that would allow you to refer to a set of columns dynamically, i.e. without explicitly naming them. If you want dynamicity, you'll need to query metadata to grab the required column names, then build the final query dynamically.

But before that you'd still need to have an idea how exactly the dynamic query should go about performing the job itself. So, you'll first to need to solve the problem on a <em>finite</em> column set.

There's more than one way to solve this problem. The <a href="https://stackoverflow.com/a/15090967/297408" rel="nofollow">method suggested by @bluefeet</a> is probably one of the clearer as well as less efficient ones. You could try at least two alternatives:

<ol><li>

Count every column separately using conditional aggregation and add up all the results in one expression:

SELECT COUNT(DATA1 > 0 OR NULL) + COUNT(DATA2 > 0 OR NULL) + COUNT(DATA3 > 0 OR NULL) + COUNT(DATA4 > 0 OR NULL) + COUNT(DATA5 > 0 OR NULL) + COUNT(DATA6 > 0 OR NULL) + COUNT(DATA7 > 0 OR NULL) AS TOTAL FROM yourtable ;

(The OR NULL trick is explained <a href="https://stackoverflow.com/questions/5011239/why-do-i-need-or-null-in-mysql-when-counting-rows-with-a-condition" title="Why do I need “OR NULL” in MySQL when counting rows with a condition" rel="nofollow">here</a>.)

</li> <li>

Unpivot the DATA columns using a cross join to a virtual table, then apply the condition to the unpivoted column:

SELECT COUNT(*) AS TOTAL FROM ( SELECT CASE s.col WHEN 'DATA1' THEN DATA1 WHEN 'DATA2' THEN DATA2 WHEN 'DATA3' THEN DATA3 WHEN 'DATA4' THEN DATA4 WHEN 'DATA5' THEN DATA5 WHEN 'DATA6' THEN DATA6 WHEN 'DATA7' THEN DATA7 END AS DATA FROM yourtable CROSS JOIN ( SELECT 'DATA1' AS col UNION ALL SELECT 'DATA2' UNION ALL SELECT 'DATA3' UNION ALL SELECT 'DATA4' UNION ALL SELECT 'DATA5' UNION ALL SELECT 'DATA6' UNION ALL SELECT 'DATA7' ) s ) s WHERE DATA > 0 ;

(In a way, this is similar to @bluefeet's suggestion, it just doesn't employ any UNIONs.)

</li> </ol>

Answer2:

Since your data is not normalized you should <em>unpivot</em> the data to get the result. MySQL does not have an unpivot function so you can use a UNION ALL query to convert your columns into rows. Once the data is in the rows, then you can easily count the number of values. I would use something similar to this:

select count(*) total from ( select id, name, 'data1' col, data1 as value from yourtable union all select id, name, 'data2' col, data2 as value from yourtable union all select id, name, 'data3' col, data3 as value from yourtable union all select id, name, 'data4' col, data4 as value from yourtable union all select id, name, 'data5' col, data5 as value from yourtable union all select id, name, 'data6' col, data6 as value from yourtable union all select id, name, 'data7' col, data7 as value from yourtable ) src where value > 0

See <a href="http://sqlfiddle.com/#!2/afa54/3" rel="nofollow">SQL Fiddle with Demo</a>

Recommend

  • How to publish a message while receiving on a Java MQTT client using Eclipse Paho
  • Get a count for the number of times a @Category appears in a suite of tests in JUnit
  • spring batch file writer to write directly to amazon s3 storage without PutObjectRequest
  • Why does `[DllImport]` fail with an entry point of `RtlSecureZeroMemory`, even though it is a well d
  • Height for UIViewControllers don't go beyond 10,000
  • I am trying to create an app in android to insert data into sql server through a web service.
  • Image insertion from SQL info
  • Read attribute/value pairs from XML file using Linq
  • Why does packing not work across sibling unions or structs
  • pass sessionid through jquery ajax call to php
  • Google Chrome compositor-driven animation affected by jam in main thread
  • Can't perform CORS request using Angularjs
  • Office.js mail object properties missing in TypeScript
  • Create One-To-One relationship between table and view in EF4?
  • Spring Cloud Config - Multiple Composite Repositories?
  • Ways of filling 10 places with number from [1..10] such that digit at ith place has value atmost 1 m
  • if you have a DLL creating a bitmap in memory, how to return it to the browser?
  • Run git bash script in Windows 7/64bit
  • what do lines starting with double-slash '//' mean in a .npmrc?
  • Could not resolve all files for configuration ':react-native-vector-icons:classpath'
  • Python ctypes: Prototype with LPCSTR [out] parameter
  • Google TV VideoView playing YouTube rtsp videos
  • Create .java file and compile it to a .class file at runtime
  • Query regarding com.jcraft.jsch.JSchException: UnknownHostKey: x.y.com. DSA key fingerprint is “ac:e
  • Facebook friend list in Facebook Android SDK 3.14
  • How to find angle formed by the blades of a wind turbine with respect to a horizontal imaginary axis
  • Connect to a local database from phpmyadmin with R
  • Add checkbox dynamically using angular 2
  • How to redirect into different page by user type in php and mysql
  • How to resolve this in PHPUnit where it is asking me to set KERNEL_DIR in my phpunit.xml?
  • Tensorflow Dataset API restore Iterator after completing one epoch
  • Python 3x- Compression Makes File Bigger :(
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod
  • JavaScript Regex to Match Boundaries of Words with diacritics
  • Bind selectedDates Aggregation for Calendar
  • Unity3d lost directional light shadows after generate assetBundle (.unity3d file)
  • How to get rgb from transparent pixel in js
  • Computing the discrete fourier transform of audio data with FFTW
  • Call Microservice from another Microservice within Docker
  • Create/delete users from text file using Bash script