CODEWITHSUNDEEP
sqlsql-server
user1464139
user1464139

Reputation:

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

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?

Upvotes: 1

Views: 64

Answers (4)

Stephen
Stephen

Reputation: 1542

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;

Upvotes: 1

shA.t
shA.t

Reputation: 16968

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;

Upvotes: 0

Svekke
Svekke

Reputation: 1530

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

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

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

Upvotes: 1

Related Questions