Ne znam sta znaci 'bez akrobacija', ali treba da pretvoris Bit u Int ili tinyInt. Upotrebi CAST ili CONVERT, otprilike ovako:
Code:
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRadnici]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [tblRadnici] (
[Radnik_ID] [int] NOT NULL ,
[ID_Odeljenje] [int] NOT NULL ,
[Plata] [money] NOT NULL ,
[BitPolje] [bit] NULL ,
CONSTRAINT [PK_tblRadnici] PRIMARY KEY CLUSTERED
(
[Radnik_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
END
GO
--- tabela tblradnici
SELECT * FROM tblRadnici
Radnik_ID ID_Odeljenje Plata BitPolje
----------- ------------ --------------------- --------
1 1 12.0000 1
2 1 15.0000 0
3 1 8.0000 1
4 1 13.0000 0
5 2 16.0000 1
6 2 12.0000 1
7 2 15.0000 1
8 2 16.0000 1
9 3 12.0000 0
10 3 10.0000 0
11 3 5.0000 0
12 3 8.0000 0
(12 row(s) affected)
--- Brojanje redova gde je VrednostBitPolja = 1
SELECT
ID_Odeljenje
, SUM(CAST ([BitPolje] AS tinyint)) AS BrojJedinica
, COUNT (*) AS BrojRedova
FROM tblRadnici
GROUP BY ID_Odeljenje
--- Moze i ovako:
SELECT
ID_Odeljenje
, SUM(CAST ([BitPolje] AS tinyint)) AS BrojJedinica
, COUNT (*) AS BrojRedova
, Resultat =
CASE
WHEN
SUM(CAST ([BitPolje] AS tinyint))= COUNT (*)
THEN 'Sve jedinice'
WHEN
SUM(CAST ([BitPolje] AS tinyint))= 0
THEN 'Sve nule'
ELSE cast(SUM(CAST ([BitPolje] AS tinyint)) AS varchar)+' jedinica'
END
FROM tblRadnici
GROUP BY ID_Odeljenje