mysql sum with named column

Question

I have table where I have two columns briefeng and briefbng, I need the count of total null and not null values in a proper way:

Here is my sql code:

SELECT
Sum(If(brief_english Is Null, 1, 0)) AS briefeng,
Sum(If(brief_english Is NOT Null, 1, 0)) AS briefengnotnull,

Sum(If(brief_bangla Is Null, 1, 0)) AS briefbng
FROM synopsis;

but it returns a result like this way:

+----------+-----------------+----------+
| briefeng | briefengnotnull | briefbng |
+----------+-----------------+----------+
|      946 |             896 |      841 |
+----------+-----------------+----------+

But I need the result in this ways

 +----------+--------------+
 | status | total          |
 +----------+--------------+
 | briefeng |         946  |
 +----------+--------------+
 | briefengnotnull |  896  |
 +----------+--------------+
 | briefengnotnull |  841  |
 +----------+--------------+

How can I do that? I couldn't find a easy and efficient way.

Use 'union' or 'union all' – Hotdin Gurning Sep 15 '15 at 08:59 — Hotdin Gurning, Sep 15 '15 at 08:59

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

Use union or union all like this :

SELECT
'briefeng' as status,
Sum(If(brief_english Is Null, 1, 0)) AS total
FROM synopsis
UNION ALL
SELECT
'briefengnotnull' as status,
Sum(If(brief_english Is NOT Null, 1, 0)) AS total
FROM synopsis
UNION ALL
SELECT
'briefbng' as status,
Sum(If(brief_bangla Is Null, 1, 0)) AS total
FROM synopsis

Difference between Union All and Union

edited May 23 '17 at 11:58

Community

1
1

answered Sep 15 '15 at 09:02

Hotdin Gurning

1,821
3
15
24

@ahmad You may take a look at this (how to use union in symfony) : http://stackoverflow.com/questions/7981549/sql-query-with-union-in-doctrine-symfony – Hotdin Gurning Sep 15 '15 at 09:27
i know that, but the problem is i dont want to create dependency with pdo rather want to stay with orm – Ahmad Sajid Sep 15 '15 at 10:06

score 0 · Answer 2 · answered Sep 15 '15 at 09:07

0

SELECT count(*) FROM tablename WHERE a IS NULL 
UNION ALL
SELECT count(*) FROM tablename WHERE a IS NOT NULL

or

select sum(case a when null then 1 else 0) "Null values",
       sum(case a when null then 0 else 1) "Non-null values"
from tablename;

or

select sum(case when a is null then 1 else 0 end) count_nulls
     , count(a) count_not_nulls 
  from tablename;

answered Sep 15 '15 at 09:07

Arunendra

570
2
10
34

none of them works i tried same things before and cant use union as symfony2 and doctrine querybuilder doesnt support union – Ahmad Sajid Sep 15 '15 at 09:15

score 0 · Answer 3 · answered Sep 15 '15 at 12:05

It's a by hook or by crook sort of approach but if you create a secondary table containing the titles with this schema

CREATE TABLE `titles` (
  `title` varchar(255) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

and this data

insert into `titles`(`title`) values ( 'briefeng'), ( 'briefengnotnull'), ( 'briefbng');

use this query

SELECT t.title, IF(a.total IS NOT NULL, a.total, IF(b.total IS NOT NULL, b.total, c.total)) AS total FROM
titles t 
LEFT JOIN (SELECT 'briefeng' AS title, SUM(IF(brief_english IS NULL, 1, 0)) AS total FROM synopsis) a ON a.title=t.title
LEFT JOIN (SELECT 'briefengnotnull' AS title, SUM(IF(brief_english IS NULL, 0, 1)) AS total FROM synopsis) b ON b.title=t.title
LEFT JOIN (SELECT 'briefbng' AS title, SUM(IF(brief_bangla  IS NULL, 1, 0)) AS total FROM synopsis) c ON c.title=t.title

to get this result (depending on the records in synopsis table)

+-----------------+-------+
| title           | total |
+-----------------+-------+
| briefbng        |     3 |
| briefeng        |     1 |
| briefengnotnull |     4 |
+-----------------+-------+

mysql sum with named column

3 Answers3