Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-01 : 17:34:40

Hi.
I have one table with some instance data:



LocationCode	ObserveCheck
-----------------------------------
X			1
X			1
X			1
X			1
X			0
Y			1
Y			1
Y			1
Z			1
Z			0
Z			0

And I want result similar to:


LocationCode	Number Of 0s	Number Of 1s
----------------------------------------------------
X		1			4	
Y		0			3	
Z		2			1

Please post any queries.
I will accept any solution (with using subquery or JOIN or PIVOT and ets).


IF OBJECT_ID (N'View_Current') IS NULL
CREATE TABLE View_Current
(
	LocationCode CHAR,
	ObserveCheck BIT	
)	

IF NOT EXISTS (SELECT * FROM View_Current) 
INSERT INTO View_Current 
SELECT LocationCode='X', ObserveCheck=1
UNION ALL
SELECT 'Y',1
UNION ALL
SELECT 'X',1
UNION ALL
SELECT 'Z',0
UNION ALL
SELECT 'Z',1
UNION ALL
SELECT 'Y',1
UNION ALL
SELECT 'X',0
UNION ALL
SELECT 'X',1
UNION ALL
SELECT 'Z',0
UNION ALL
SELECT 'Y',1
UNION ALL
SELECT 'X',1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-01 : 17:54:37

[code]DECLARE @Sample TABLE
(
LocationCode CHAR(1) NOT NULL,
ObserveCheck TINYINT NOT NULL
)

INSERT @Sample
SELECT 'X', 1 UNION ALL
SELECT 'X', 1 UNION ALL
SELECT 'X', 1 UNION ALL
SELECT 'X', 1 UNION ALL
SELECT 'X', 0 UNION ALL
SELECT 'Y', 1 UNION ALL
SELECT 'Y', 1 UNION ALL
SELECT 'Y', 1 UNION ALL
SELECT 'Z', 1 UNION ALL
SELECT 'Z', 0 UNION ALL
SELECT 'Z', 0

SELECT LocationCode,
SUM(1 - ObserveCheck) AS [0],
SUM(ObserveCheck) AS [1]
FROM @Sample
GROUP BY LocationCode
ORDER BY LocationCode

SELECT LocationCode,
COUNT(*) - SUM(ObserveCheck) AS [0],
SUM(ObserveCheck) AS [1]
FROM @Sample
GROUP BY LocationCode
ORDER BY LocationCode[/code]

N 56°04'39.26"
E 12°55'05.63"

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-01 : 17:58:55

Thanks a lot Peso.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-01 : 19:24:07

Imagine data type of ObserveCheck column is CHAR
then your query will not be useful.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-01 : 19:46:07

Why would I imagine that?
Does your sample data not represent your actual environment?
You have started a goose chase?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-01 : 19:47:08

[code]SELECT LocationCode,
SUM(CASE WHEN ObserveCheck = '0' THEN 1 ELSE 0 END) AS [0],
SUM(CASE WHEN ObserveCheck = '1' THEN 1 ELSE 0 END) AS [1]
FROM @Sample
GROUP BY LocationCode
ORDER BY LocationCode[/code]

N 56°04'39.26"
E 12°55'05.63"

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-01 : 20:09:20

Excuse me.

your query is really summary and great.


SELECT LocationCode, 
		(CASE WHEN [0] IS NULL THEN 0 ELSE [0] END) AS [0],
		(CASE WHEN [1] IS NULL THEN 0 ELSE [1] END) AS [1]
FROM (
		SELECT D1.LocationCode, 
				(SELECT COUNT(*)  
					FROM @Sample 
					WHERE LocationCode=D1.LocationCode 
					AND ObserveCheck=0 
					GROUP BY LocationCode) AS [0] ,
				(SELECT COUNT(*)  
					FROM @Sample 
					WHERE LocationCode=D1.LocationCode 
					AND ObserveCheck=1 
					GROUP BY LocationCode) AS [1]
		FROM (
				SELECT DISTINCT LocationCode
				FROM @Sample
			) AS D1
	) AS D2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources