Count Help - 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)

Count Help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-11-16 : 16:10:50

[code]
I need a query for counts.If there is a more than one column data filled for the SID then the count should update to more than oneC

TableA
-------

SID Col1 Col2 Col3 Col4 Col5
--- ---- ---- ---- ---- ----
1 C1
2 C2
3 C3
4 C4 C5
5 C1
6 C1
7 C1 C5

TableM
------

MID Desc
--- ----
C1 Cdesc1
C2 Cdesc2
C3 Cdesc3
C4 Cdesc4
C5 Cdesc5

I want the output

Desc Count
------ -----
Cdesc1 3
Cdesc2 1
Cdesc3 1
Cdesc4 0
Cdesc5 0
MorethanoneC 2

Thanks for yur help in advance..

[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-16 : 16:13:47

Your output doesn't make sense based upon the description that you have given.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-11-16 : 16:36:36

SID 4 has two values c4,c5 and
SID 7 has two values c1,c5 in that case i want to update the count for desc :MorethanoneC
If there is one value for the SID then it should the udpate the count to the respective desc.

In the TableA:



TableA
-------

SID Col1 Col2 Col3 Col4 Col5
--- ---- ---- ---- ---- ----
1    C1
2         C2
3              C3
4                    C4   C5
5    C1
6    C1
7    C1                    C5

Please let me know if i m not clear.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-11-16 : 16:36:36



TableA
-------

SID Col1 Col2 Col3 Col4 Col5
--- ---- ---- ---- ---- ----
1    C1
2         C2
3              C3
4                    C4   C5
5    C1
6    C1
7    C1                    C5

Please let me know if i m not clear.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-16 : 16:49:00

I get the counting of MorethanoneC, however I don't understand how you came up with the other numbers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-11-16 : 17:12:46

[code]

SID Col1 Col2 Col3 Col4 Col5
--- ---- ---- ---- ---- ----
1 C1
2 C2
3 C3
4 C4 C5
5 C1
6 C1
7 C1 C5

Desc Count
------ -----
Cdesc1 3

quote:
SID:1,5,6

Cdesc2 1

quote:
SID:2

Cdesc3 1

quote:
SID:3

Cdesc4 0

quote:
Count will be zero Since SID:4 two values in the row

Cdesc5 0

quote:
Count will be zero Since SID:7 two values in the row

MorethanoneC 2

quote:
SID:4,7

[/code]

Please let me know if i am not clear..
I want to update the count to the respective description if the row (SID) has one value.If the row (SID) has two values then the count should updates to MorethanoneC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-16 : 17:26:19

Yes it is now clear. It's late on a Friday now though, so I probably won't start working on a solution until Monday. Hopefully someone else helps you out sooner.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2007-11-16 : 17:36:03

<CODE>
select
sum(case when C1 <> '' and C2 = '' and C3 = '' and C4 = '' and C5 = '' then 1 else 0 end),
sum(case when C1 = '' and C2 <> '' and C3 = '' and C4 = '' and C5 = '' then 1 else 0 end),
sum(case when C1 = '' and C2 = '' and C3 <> '' and C4 = '' and C5 = '' then 1 else 0 end),
sum(case when C1 = '' and C2 = '' and C3 = '' and C4 <> '' and C5 = '' then 1 else 0 end),
sum(case when C1 = '' and C2 = '' and C3 = '' and C4 = '' and C5 <> '' then 1 else 0 end),
sum(case when C2 <> '' and (C2 <> '' or C3 <> ''...)
or C2 <> '' and (C3 <> '' or C4 <> ''...)
or C3 <> '' and (C4 <> '' or C5 <> '')
or C4 <> '' C5 <> '' then 1 else 0 end)
from TableA

=======================================
The only man I know who behaves sensibly is my tailor; he takes my measurements anew each time he sees me. The rest go on with their old measurements and expect me to fit them. -George Bernard Shaw, writer, Nobel laureate (1856-1950)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-16 : 19:11:19

This is bit messy. Probably there is a better way of doing this

DECLARE @TableA TABLE
(
	SID 	int,
	Col1	varchar(2),
	Col2 	varchar(2),
	Col3 	varchar(2),
	Col4 	varchar(2),
	Col5	varchar(2)
)
INSERT INTO @TableA 
SELECT	1,    'C1', NULL, NULL, NULL, NULL	UNION ALL
SELECT	2,    NULL, 'C2', NULL, NULL, NULL	UNION ALL
SELECT	3,    NULL, NULL, 'C3', NULL, NULL	UNION ALL
SELECT	4,    NULL, NULL, NULL, 'C4', 'C5'	UNION ALL
SELECT	5,    'C1', NULL, NULL, NULL, NULL	UNION ALL
SELECT	6,    'C1', NULL, NULL, NULL, NULL	UNION ALL
SELECT	7,    'C1', NULL, NULL, NULL, 'C5'


DECLARE @TableM TABLE
(
	MID	varchar(2),
	[DESC]	varchar(6)
)
INSERT INTO @TableM
SELECT	'C1',  'Cdesc1'	UNION ALL
SELECT	'C2',  'Cdesc2'	UNION ALL
SELECT	'C3',  'Cdesc3'	UNION ALL
SELECT	'C4',  'Cdesc4'	UNION ALL
SELECT	'C5',  'Cdesc5'

DECLARE @TableT TABLE
(
	SID	int,
	Col	varchar(2)
)

INSERT INTO @TableT
SELECT	SID, Col1 AS Col FROM	@TableA	WHERE Col1 IS NOT NULL UNION ALL
SELECT	SID, Col2 AS Col FROM	@TableA	WHERE Col2 IS NOT NULL UNION ALL
SELECT	SID, Col3 AS Col FROM	@TableA	WHERE Col3 IS NOT NULL UNION ALL
SELECT	SID, Col4 AS Col FROM	@TableA	WHERE Col4 IS NOT NULL UNION ALL
SELECT	SID, Col5 AS Col FROM	@TableA WHERE Col5 IS NOT NULL 


SELECT	m.[DESC], Cnt = COUNT(t.Col)
FROM	@TableM m
	left JOIN 
	(
		SELECT	t.SID, t.Col
		FROM	@TableT t	
			left JOIN
			(
				SELECT	SID
				FROM	@TableT
				WHERE	Col	IS NOT NULL
				GROUP BY SID
				HAVING COUNT(*) > 1
			) n	ON	t.SID	= n.SID
		WHERE	n.SID	IS NULL
	) t	ON	m.MID	= t.Col
GROUP BY m.[DESC]

UNION ALL

SELECT	'MoreThanOneC' AS [DESC], COUNT(*)
FROM
(
	SELECT 	SID
	FROM	@TableT
	WHERE	Col	IS NOT NULL
	GROUP BY SID
	HAVING COUNT(*) > 1
) a

KH
[spoiler]Time is always against us[/spoiler]

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-11-19 : 10:07:17

Thanks khtan!
But if there are more records in table then probably there might be performance issues may occur
Is there any better way of doing.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 10:10:21

yes. Redesign your table. Normalized it

KH
[spoiler]Time is always against us[/spoiler]

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-11-19 : 11:46:26

KH !
Probably now its not possible to redesign the database.
I may go with your solution.
How to i extend the query if i want to add where condition filters


DECLARE @TableA TABLE
(
	SID 	int,
	Col1	varchar(2),
	Col2 	varchar(2),
	Col3 	varchar(2),
	Col4 	varchar(2),
	Col5	varchar(2),
        Fdate   datetime 
)

INSERT INTO @TableA 
SELECT	1,    'C1', NULL, NULL, NULL, NULL,'2006-08-12 00:00:00.000'UNION ALL
SELECT	2,    NULL, 'C2', NULL, NULL, NULL,'2006-09-12 00:00:00.000'	UNION ALL
SELECT	3,    NULL, NULL, 'C3', NULL, NULL,'2006-08-12 00:00:00.000' UNION ALL
SELECT	4,    NULL, NULL, NULL, 'C4', 'C5','2006-09-12 00:00:00.000' UNION ALL
SELECT	5,    'C1', NULL, NULL, NULL, NULL,'2006-010-12 00:00:00.000'	UNION ALL
SELECT	6,    'C1', NULL, NULL, NULL, NULL,'2006-08-12 00:00:00.000'	UNION ALL
SELECT	7,    'C1', NULL, NULL, NULL, 'C5','2006-08-12 00:00:00.000'

I want to get the counts based on the dates.
How do i extend the where filter in the query.
Thanks for yur help in advanced!!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 11:58:25

Add the WHERE CLAUSE into the "INSERT INTO @TableT" statement

KH
[spoiler]Time is always against us[/spoiler]

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-11-19 : 12:18:34

sorry if i m not clear.
I want to pass the input parameter with fromdate and todate
based on the values in fdate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources