Jump to content
Sign in to follow this  
Lainkes

Retrieve count values on different columns

Recommended Posts

Hello,


I have a database with a lot of fields. There can have the value True of False.

Now I want to count the number of records with True values per Field. I can do this per field.

But then I have more than 50 SQL statements.

SQL.Text := 'select count(*) from tablename where FIELDNAME1 = :FIELDVALUE1 ';
ParamByName('FIELDVALUE1').AsString := 'True';

I was wondering if there could be a faster way to retrieve these in one SQL statement.

 

Or is the way I'm working acceptable?

 

Thanks for your advise.

 

Lainkes

Share this post


Link to post

you 50 tables, right? you can try use "UNION"  like this:

 

Quote

select FieldXXX01Boolean as MyBoo from TableA

UNION ALL

select FieldXXX02Boolean as MyBoo from TableB

.... + UNION

where MyBoo = TRUE

.... ORDER BY MyBoo  // or BY 1

FieldXXXnnBoolean = always same type

MyBoo = common name for it in final recordset

TableX = each table or same table (if all fields is in)

 

now, just COUNT( ) your final recordset field MyBoo...  or in each SELECT (avoiding many rows on final recordset)

 

Quote

 

 

 

Screenshot_20230302-170829-158.png

Edited by programmerdelphi2k

Share this post


Link to post

Which SQL Engine?


One table scan for whatever you want.
 

-- Assuming that Field type is TINYINT NOT NULL 1/0
SELECT
  SUM(T.Field1) AS SumField1,
  SUM(T.Field2) AS SumField2,
  ..
FROM
  Table1 AS T;


-- If you want special values. Or you can use IIF()
SELECT
  SUM(CASE WHEN T.Field1 = 'SomeTextValue' THEN 1 ELSE 0 END)) AS SumField1,
  SUM(CASE WHEN T.FieldMixed = 7 THEN 1 ELSE 0 END)) AS SumMixed7,
  SUM(CASE WHEN T.FieldMixed = 31 THEN 1 ELSE 0 END)) AS SumMixed31
  ..
FROM
  Table1 AS T;

Optionally if you prefer rows instead of columns you can use UNPIVOT + SUM + GROUP BY (for MSSQL, I don't know with other SQL engines that much but probably there is similar feature too)

Edited by DiGi
  • Like 1

Share this post


Link to post

I'm using MariaDB.

I have 1 table. In that table are 50 fields (string) that can be True or False.

I want a count of the True value for every field for a particular year.

I thought that SUM was adding values, not counting. Am I correct?


Thanks for your feedback

 

Lainkes

 

Share this post


Link to post
1 hour ago, Lainkes said:

I'm using MariaDB.

I have 1 table. In that table are 50 fields (string) that can be True or False.

I want a count of the True value for every field for a particular year.

I thought that SUM was adding values, not counting. Am I correct?


Thanks for your feedback

 

Lainkes

 

Right, but you can convert boolean to integer (1/0) and then SUM works like COUNTIF. Conversion can be implicit or explicit, or using case/iif constructs... whatever MariaDB supports.

 

Share this post


Link to post

https://mariadb.com/kb/en/boolean/ - so it is TINYINT(1)

 

You can wrote simple

SELECT
  T.YourYearColumn, -- or whatever
  SUM(T.Option1) AS CountRowsWithOption1,
  SUM(T.Option2) AS CountRowsWithOption2,
  ..
FROM
  Table1 AS T
GROUP BY
  T.YourYearColumn;

Just try it by yourself and you will see results.

Share this post


Link to post

How about:

 

SELECT if(col1 = 'True', 1, 0) + if(col2 = 'True', 1, 0) } + if(col3 = 'True', 1, 0) } ... AS "Sum Of True Strings" FROM ...

Share this post


Link to post

Or:

 

SELECT SUM( if( (if(col1 = 'True', 1, 0) + if(col2 = 'True', 1, 0) } + if(col3 = 'True', 1, 0) ... ) > 0, 1, 0) ) AS "Some Columns Have True Strings" FROM ...

Edited by weirdo12
Correction

Share this post


Link to post

Hmm... 50 columns? So why not concatenate all of them and get the combined length, subtract this from 50 * 5 (length of "False") and you get the count of "true". 

Share this post


Link to post
13 hours ago, Frickler said:

Hmm... 50 columns? So why not concatenate all of them and get the combined length, subtract this from 50 * 5 (length of "False") and you get the count of "true". 

Nice. The only issue would be if any of the columns is NULL.

Share this post


Link to post

Just generate SQL in code basing on your structure, pattern is like

select * from
(select count(*) as cnt1 from DOWNLOADS where CHAR_LENGTH(REMPATH) > 60)
join
(select count(*) as cnt2 from DOWNLOADS where CHAR_LENGTH(LOCPATH) > 50)
on 1=1
join
(select count(*) as cnt3 from DOWNLOADS where FSIZE > 50000)
on 1=1

(names are from my test DB but you should get the idea)

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×