How can I count multiple columns in a query with a row for each column count?

Question

I have a table that looks like this:

CREATE TABLE [dbo].[Phrase] 
(
    [PhraseId]     UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [English]      NVARCHAR (250)   NOT NULL,
    [p1]           BIT              DEFAULT ((0)) NOT NULL,
    [p2]           BIT              DEFAULT ((0)) NOT NULL,
    [p3]           BIT              DEFAULT ((0)) NOT NULL, 

    PRIMARY KEY CLUSTERED ([PhraseId] ASC),

    CONSTRAINT [FK_PhrasePhraseChapter] 
        FOREIGN KEY ([ChapterId]) 
        REFERENCES [dbo].[PhraseChapter] ([PhraseChapterShortId])
);

What I would like to do is to get a count of p1, p2 and p3 so the output looks something like:

p1  100
p2  200
p3  23

Note that in this case the first row would mean that there were 100 rows in the Phrase table that had p1 set to a value of 1. Hope this makes sense.

I know I could do this simply with three columns but I need a row output of just two string columns like above. Can anyone give me a suggestion as to how I could achieve this?

Tanner · Accepted Answer · 2017-01-27 10:05:06Z

1

You can use UNPIVOT, like so:

SELECT  myColumn ,
        NoOfTrue
FROM    ( SELECT    [p1] = COUNT(CASE WHEN [p1] = 1 THEN 1 END) ,
                    [p2] = COUNT(CASE WHEN [p2] = 1 THEN 1 END) ,
                    [p3] = COUNT(CASE WHEN [p3] = 1 THEN 1 END)
          FROM      Phrase
        ) d UNPIVOT
    ( NoOfTrue FOR myColumn IN ( [p1], [p2], [p3] ) ) piv;

edited Jan 27, 2017 at 10:05

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

answered Jan 27, 2017 at 9:53

Stephen

1,5421 gold badge9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tanner Over a year ago

Yeah, I think this is the best approach, I was working on something similar, the only difference was I did [p1] = SUM(CAST(p1 AS INT)) instead of using the case statements. +1

JohnHC · Accepted Answer · 2017-01-27 09:21:48Z

1

Use a union:

select 'P1' as ColToCount,
       count(distinct P1) as Counted
from MyTable
where 1=1 -- replace with any criteria
union all
select 'P2' as ColToCount,
       count(distinct P2) as Counted
from MyTable
where 1=1 -- replace with any criteria
union all
select 'P3' as ColToCount,
       count(distinct P3) as Counted
from MyTable
where 1=1 -- replace with any criteria

This is only really good if you only have a few different things to count

answered Jan 27, 2017 at 9:21

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Comments

Svekke · Accepted Answer · 2017-01-27 09:23:12Z

0

Perhaps by using case and sum:

SELECT sum(case when p1 = 1 then 1 end) as p1Count, 
       sum(case when p2 = 1 then 1 end) as p2Count,
       sum(case when p3 = 1 then 1 end) as p3Count
FROM Phrase

answered Jan 27, 2017 at 9:23

Svekke

1,5501 gold badge14 silver badges20 bronze badges

1 Comment

Tanner Over a year ago

this does not give the desired output as leaves the data in the original columns

shA.t · Accepted Answer · 2017-01-27 09:24:37Z

0

simplest way is:

select 'p1' p, sum(p1) countp
from Phrase
union all
select 'p2', sum(p2)
from Phrase
union all
select 'p3', sum(p3)
from Phrase;

Note using sum for a bit field can do all that you want.
A bit field can have (0-1-null) values and sum will add null values as 0;

answered Jan 27, 2017 at 9:24

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

Collectives™ on Stack Overflow

How can I count multiple columns in a query with a row for each column count?

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related