two counts based on field value

Question

I have a table table1 that has a field1 has values from 1 to 5 I need to get the count of records when field1 = 1 and the count of remaining records when field1 <> 1. so I could do it like this:

select count(*) from table1
group by field1 = 1

however this will give me the two correct values as two records in the result but I wonder is there a way to get the two count values as columns in one select SQL ?

How is Arioch's answer correct? It has one column with the count of 1's, your question asked for a count of 1's AND a count of non-1's in two columns. I'm not sure how the answer that results in one row and doesn't count the non-ones addresses your question. — billpennock, Dec 15 '16 at 14:37
his answer has two columns, with the use of `case` in one SQL — zac, Dec 15 '16 at 14:42
Oh geesshh...for some reason the scroll bar didn't show up when I looked at this the first time and my UI only went to the first row of the first query which was data 3 and "true". I should have recognized that there must have been more inspite of the lack of scroll considering it had to have the second row. So, egg on face, you liked his answer better. cool. — billpennock, Dec 16 '16 at 15:40

score 2 · Answer 1 · edited Dec 06 '16 at 14:45

2

create table onetwofive (field1 int)
go
insert into onetwofive (field1) values
    (1),(2),(3),(4),(5),(1),(1),(2)
go
select (select count(*) from onetwofive where field1 = 1) ones,
       (select count(*) from onetwofive where field1 <> 1) others
       from rdb$database

edited Dec 06 '16 at 14:45

ain

22,394
3
54
74

answered Dec 06 '16 at 14:29

billpennock

441
1
6
14

there is no need for sub-selects :-P – Arioch 'The Dec 09 '16 at 21:38

score 1 · Answer 2 · answered Dec 06 '16 at 15:22

1

If you don't want to create the intermediate table, you also have the sub-query solution. This one should do what you're looking for:

select distinct * from
(select count(*) as ones from table1 group by field1 having field1 = 1) as ones 
join 
(select count(*) as not_ones from table1 group by field1 having field1 <> 1) as not_ones;

You should get something like below:

+------+----------+
| ones | not_ones |
+------+----------+
|  9   |      6   |
+------+----------+
1 row in set (0,00 sec)

Hope this helps.

answered Dec 06 '16 at 15:22

David Verdin

480
6
18

there is no need for sub-selects :-P – Arioch 'The Dec 09 '16 at 21:38
Really? You mean I could simply soustract the count from the total number of records? But how to do this in a single query? – David Verdin Dec 11 '16 at 21:58
just look in this very page in my answer. You have to run two counters in parallel – Arioch 'The Dec 14 '16 at 12:19
I didn't notice you had answered. Nice solution. I'll think about it when in a similar situation. Like always: knowing is good. ;-) I tend to use sub-queries a lot, failing to see simpler solutions. – David Verdin Dec 14 '16 at 12:35

Arioch 'The · Accepted Answer · 2016-12-14T12:25:07.947

You do not need sub-selects actually. It is enough to use one query.

Hint: SQL-standard aggregate-functions ignore all-NULL values.

c:\Program Files\Firebird\Firebird_3_0>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database "b:\testfb3.fdb" ;

SQL> create table oneTwoFive (field1 int);

SQL> set bulk_insert insert into onetwofive(field1) values (?);
BULK> (1)
BULK> (2)
BULK> (3)
BULK> (4)
BULK> (5)
BULK> (1)
BULK> (1)
BULK> (2)
BULK>

SQL> select * from onetwofive;

      FIELD1
============
           1
           2
           3
           4
           5
           1
           1
           2

SQL> select distinct count(*), field1=1 as "Is One Flag" from onetwofive
CON> group by 2;

                COUNT Is One Flag
===================== ===========
                    3 <true>
                    5 <false>

SQL> select
CON> count(case when field1=1 then 1 else null end) "When One",
CON> count(case when field1=1 then null else 1 end) "Rest"
CON> from oneTwoFive;

             When One                  Rest
===================== =====================
                    3                     5

two counts based on field value

3 Answers3