Count the record in SQL - 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 the record in SQL

Author

Topic

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-10-06 : 06:00:33

This is sample data.
Company Region ServiceType MTDCount YesCount
Maruti NULL BUG IM17778
Maruti NULL BUG IM17813
Maruti NULL BUG IM17828
Maruti NULL BUG IM17873
Maruti NULL BUG IM17873
Maruti NULL BUG IM17909
Maruti NULL BUG IM17909

I want the following result.

Company Region ServiceType MTDCount YesCount
Maruti NULL BUG 0 5

Pls Help.Very Urgent

V.NAGARAJAN

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-06 : 06:08:45

quote:

Company Region ServiceType MTDCount YesCount

The header is for 5 column but data is for 4 only. You are missing data either for MTDCount or YesCount. Please correct it.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 06:11:21

quote:

Pls Help.Very Urgent

IN WHAT POSSIBLE WAY IS THIS VERY UREGENT?

Is your server down because of this? No.

You want to run some sort of report. Not urgent at all.

Looks like you want a count of the distinct YesCounts per Company / Region.

So a derived table with a group by and a COUNT is what you want.

Work out the rest yourself.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-10-06 : 06:12:13

MTDCount[Fourth Column] is Empty.So that count is zero in result set.

quote:
Originally posted by pk_bohra

quote:

Company Region ServiceType MTDCount YesCount

The header is for 5 column but data is for 4 only. You are missing data either for MTDCount or YesCount. Please correct it.

V.NAGARAJAN

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 06:31:29

quote:
Originally posted by Transact Charlie

IN WHAT POSSIBLE WAY IS THIS VERY UREGENT?

Is your server down because of this? No.

You want to run some sort of report. Not urgent at all.

Looks like you want a count of the distinct YesCounts per Company / Region.

So a derived table with a group by and a COUNT is what you want.

Work out the rest yourself.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

You dont seem to be in a good mood today

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 07:07:38

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by Transact Charlie

IN WHAT POSSIBLE WAY IS THIS VERY UREGENT?

Is your server down because of this? No.

You want to run some sort of report. Not urgent at all.

Looks like you want a count of the distinct YesCounts per Company / Region.

So a derived table with a group by and a COUNT is what you want.

Work out the rest yourself.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

You dont seem to be in a good mood today

PBUH

Don't like URGENT when its not. And I'm getting a lot of **** today at work.

itnagaraj: Sorry for being grumpy. Does this work for you?



DECLARE @foo TABLE (
	[Company] VARCHAR(255)
	, [Region] VARCHAR(255)
	, [ServiceType] CHAR(3)
	, [MTDCount] INT
	, [YesCount] VARCHAR(255)
	)
INSERT @foo
      SELECT 'Maruti',	NULL,	'BUG',	0,	 'IM17778'
UNION SELECT 'Maruti',	NULL,	'BUG',	0,	 'IM17813'
UNION SELECT 'Maruti',	NULL,	'BUG',	0,	 'IM17828'
UNION SELECT 'Maruti',	NULL,	'BUG',	0,	 'IM17873'
UNION SELECT 'Maruti',	NULL,	'BUG',	0,	 'IM17873'
UNION SELECT 'Maruti',	NULL,	'BUG',	0,	 'IM17909'
UNION SELECT 'Maruti',	NULL,	'BUG',	0,	 'IM17909'

SELECT
	[Company]
	, [Region]
	, [ServiceType]
	, [MTDCount]
	, COUNT(DISTINCT([YesCount])) AS [YesCount]
FROM
	@foo
GROUP BY
	[Company]
	, [Region]
	, [ServiceType]
	, [MTDCount]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-10-06 : 08:12:34

Find out the solution.Don't speak anyother.

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by Transact Charlie

IN WHAT POSSIBLE WAY IS THIS VERY UREGENT?

Is your server down because of this? No.

You want to run some sort of report. Not urgent at all.

Looks like you want a count of the distinct YesCounts per Company / Region.

So a derived table with a group by and a COUNT is what you want.

Work out the rest yourself.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

You dont seem to be in a good mood today

PBUH

V.NAGARAJAN

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 08:26:47

quote:
Find out the solution.Don't speak anyother.

What???

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 09:15:06

quote:
Originally posted by itnagaraj

Find out the solution.Don't speak anyother.

Are you actually telling me to "do my job for me!"?

Wow.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 10:56:57

quote:
Originally posted by Transact Charlie

quote:
Originally posted by itnagaraj

Find out the solution.Don't speak anyother.

Are you actually telling me to "do my job for me!"?

Wow.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

You reap what you sow(outsourcing).

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources