Reputation: 415
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
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