CODEWITHSUNDEEP
sqlsql-server-2008t-sql
b3bel
b3bel

Reputation: 415

how to have a column count the number of times a BIT field is zero and another column counting the number of time it is one?

I have the following table:

CREATE TABLE [dbo].[PartsWork](
    [parts_work_id] [int] IDENTITY(1,1) NOT NULL,
    [experiment_id] [int] NOT NULL,
    [partition_id] [int] NULL,
    [part_id] [int] NOT NULL,
    [sim_time] [int] NULL,
    [real_time] [datetime] NULL,
    [construction] [bit] NULL,
    [destruction] [bit] NULL,
    [duration] [int] NULL )

I want to write a SQL that outputs the part_id, number of times construction is = 1 and number of times destruction is = 1 for a given experiment_id AND partition_id.

I can do this using multi-statement table valued functions and a loop, but I would like to be able to do this in a single SQL. Is this possible?

Upvotes: 1

Views: 92

Answers (1)

gbn
gbn

Reputation: 432742

To give all part_id values, you need the OVER clause on the COUNT (this is an inline aggregation without using GROUP BY)

SELECT
    part_id,
    COUNT(CASE WHEN construction = 1 THEN 1 END) OVER () AS CountConstructionIs1,
    COUNT(CASE WHEN destruction = 1 THEN 1 END) OVER () AS CountDestructionIs1
FROM
   [dbo].[PartsWork]
WHERE
    experiment_id = @experiment_id
    AND
    partition_id = @partition_id

See MSDN for more

Upvotes: 1

Related Questions