Query Issue - 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 2000 Forums

SQL Server Development (2000)

Query Issue

Author

Topic

kkruk
Starting Member

1 Post

Posted - 2005-11-28 : 11:05:09

Hello all

I am having a problem and I hope you guys could help me with it. Essentially, I have to create a query. This essentially what my db tables look like.

----------------
| Name | Status|
----------------
|First | 1 |
|First | 2 |
|Second| 3 |
|Second| 2 |
|Third | 1 |
----------------

From this table, I have to get the following table:

--------------------------------------------
|Name |Count Total|Count 1|Count 2|Count 3|
--------------------------------------------
|First | 2 | 1 | 1 | 0 |
|Second| 2 | 0 | 1 | 1 |
|Third | 1 | 1 | 0 | 0 |
--------------------------------------------

Here, "Count Total" is the total number of entries for each name, "Count 1" is the total number of entries with the Status 1 for each name, "Count 2" is for status 2, and so on.

I have a feeling that this quiry can be done fairly easily, but I cannot envision it. Any help you guys could give would be great.

Thank you in advance.

cfederl
Starting Member

26 Posts

Posted - 2005-11-28 : 11:40:52

See "How do I create a cross-tab (or "pivot") query?" at http://www.aspfaq.com/show.asp?id=2462

Carl Federl

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-28 : 11:58:09

Want to tell me why?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([Name] varchar(10), Status int)
GO

INSERT INTO myTable99([Name], Status)
SELECT 'First' , 1 UNION ALL
SELECT 'First' , 2 UNION ALL
SELECT 'Second', 3 UNION ALL
SELECT 'Second', 2 UNION ALL
SELECT 'Third' , 1
GO

   SELECT a.[Name], a.[Count Total], COALESCE(b.[Count 1],0), COALESCE(c.[Count 2],0), COALESCE(d.[Count 3],0)
     FROM ( SELECT [Name], COUNT(*) AS [Count Total] 
	      FROM myTable99 
	  GROUP BY [Name]) AS a
LEFT JOIN ( SELECT [Name], COUNT(*) AS [Count 1]
	      FROM myTable99
	     WHERE Status = 1
	  GROUP BY [Name]) AS b
       ON a.[Name] = b.[Name]
LEFT JOIN ( SELECT [Name], COUNT(*) AS [Count 2]
	      FROM myTable99
	     WHERE Status = 2
	  GROUP BY [Name]) AS c
       ON a.[Name] = c.[Name]
LEFT JOIN ( SELECT [Name], COUNT(*) AS [Count 3]
	      FROM myTable99
	     WHERE Status = 3
	  GROUP BY [Name]) AS d
       ON a.[Name] = d.[Name]

GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-28 : 15:32:39

Here is an alternative approach that is pretty straightforward:[CODE]
select 'First' as Name, Sum(1) as TotalCount, Sum(case when Status = 1 then 1 else 0 end) as Count1, Sum(case when Status = 2 then 1 else 0 end) as Count2, Sum(case when status = 3 then 1 else 0 end) as Count3
from mytable99
where Name = 'First'
UNION ALL
select 'Second', Sum(1) as TotalCount, Sum(case when Status = 1 then 1 else 0 end) as Count1, Sum(case when Status = 2 then 1 else 0 end) as Count2, Sum(case when status = 3 then 1 else 0 end) as Count3
from mytable99
where Name = 'Second'
UNION ALL
select 'Third', Sum(1) as TotalCount, Sum(case when Status = 1 then 1 else 0 end) as Count1, Sum(case when Status = 2 then 1 else 0 end) as Count2, Sum(case when status = 3 then 1 else 0 end) as Count3
from mytable99
where Name = 'Third'[/CODE]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-29 : 01:51:20

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-29 : 04:51:01

If there are only a few statuses, maybe a join query would still be efficient, but it's not very dynamic.

Select t.[Name], count(*), t1.Count1, t2.Count2, t3.Count3 from Temp1 t
left join (Select count(*) 'Count1', Name from Temp1 where Status = 1 group by Name) t1 on t1.[Name] = t.[Name]
left join (Select count(*) 'Count2', Name from Temp1 where Status = 2 group by Name) t2 on t2.[Name] = t.[Name]
left join (Select count(*) 'Count3', Name from Temp1 where Status = 3 group by Name) t3 on t3.[Name] = t.[Name]
group by t.[Name], t1.Count1, t2.Count2, t3.Count3

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-29 : 21:08:07

I happened to think there is no reason to make it as complicated as I had. The following will work just fine:

Select Name, Sum(1) as TotalCount, Sum(case when Status = 1 then 1 else 0 end) as Count1, Sum(case when Status = 2 then 1 else 0 end) as Count2, Sum(case when status = 3 then 1 else 0 end) as Count3
from mytable99
group by name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources