Retrieve count values on different columns

[Lainkes 0]

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

[programmerdelphi2k 237]

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

 

 

 

Edited March 2, 2023 by programmerdelphi2k

[DiGi 14]

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 March 2, 2023 by DiGi

[Lainkes 0]

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

 

[Zoran Bonuš 12]

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.

 

[DiGi 14]

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.

[weirdo12 19]

How about:

 

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

[weirdo12 19]

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 March 4, 2023 by weirdo12
Correction

[Frickler 11]

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". 

[weirdo12 19]

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.

[Fr0sT.Brutal 900]

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)