SELECT COUNT problem - 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

Transact-SQL (2000)

SELECT COUNT problem

Author

Topic

Pat Shaw
Starting Member

14 Posts

Posted - 2006-06-16 : 08:49:16

I have the following query:

SELECT COUNT(*) as 'Total'
FROM dbo.CallRslt
WHERE CallResultCode IN
(SELECT CallResultCode FROM Mcalcode
WHERE Presentation = 1)
AND CONVERT(VarChar, DateofCall, 103) = CONVERT(Varchar, GETDATE()-1, 103)

In the CallRslt table used in the above, there is a column called 'PhoneNum'. I need to add a COUNT of this column to the SELECT in the query but it must include WHERE PhoneNum = '01********25'.

How can I add this in to the select?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-16 : 08:54:14

Is this what you want ? If not please, post the table structure, same sample data and the result that you want

SELECT COUNT(*) as 'Total'
FROM   dbo.CallRslt
WHERE  CallResultCode IN
      (SELECT CallResultCode FROM Mcalcode
              WHERE Presentation = 1)
AND   CONVERT(VarChar, DateofCall, 103) = CONVERT(Varchar, GETDATE()-1, 103)
AND   PhoneNum like '01%'
AND   PhoneNum like '%25'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 09:09:01

quote:

Originally posted by khtan

SELECT COUNT(*) as 'Total'
FROM   dbo.CallRslt
WHERE  CallResultCode IN
      (SELECT CallResultCode FROM Mcalcode
              WHERE Presentation = 1)
AND   CONVERT(VarChar, DateofCall, 103) = CONVERT(Varchar, GETDATE()-1, 103)
AND   PhoneNum like '01%'
AND   PhoneNum like '%25'

If I write

SELECT		COUNT(*) 'Total'
FROM		dbo.CallRslt 
INNER JOIN	Mcalcode ON Mcalcode.CallResultCode = dbo.CallRslt.CallResultCode
WHERE		Mcalcode.Presentation = 1
		AND CONVERT(VARCHAR, DateofCall, 103) = CONVERT(VARCHAR, GETDATE() - 1, 103)
		AND PhoneNum LIKE '01%25'

How would that impact speed?

Peter Larsson
Helsingborg, Sweden

Pat Shaw
Starting Member

14 Posts

Posted - 2006-06-16 : 09:10:57

No, I need 2 columns in the results, 1 for the COUNT(*) and 1 for the COUNT(PhoneNum).

Logically my query should look something like:

SELECT COUNT(*) as 'Total', (COUNT(PhoneNum) WHERE PhoneNum = '01********25')
FROM dbo.CallRslt
WHERE CallResultCode IN
(SELECT CallResultCode FROM Mcalcode
WHERE Presentation = 1)
AND CONVERT(VarChar, DateofCall, 103) = CONVERT(Varchar, GETDATE()-1, 103)

but this of course will not work because of the WHERE in the SELECT.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 09:17:11

[code]SELECT COUNT(*) 'Total',
SUM(CASE WHEN PhoneNum LIKE '01%25' THEN 1 ELSE 0 END) 'Subset'
FROM dbo.CallRslt
INNER JOIN Mcalcode ON Mcalcode.CallResultCode = dbo.CallRslt.CallResultCode
WHERE Mcalcode.Presentation = 1
AND CONVERT(VarChar, DateofCall, 103) = CONVERT(Varchar, GETDATE() -1, 103)[/code]
Peter Larsson
Helsingborg, Sweden

Pat Shaw
Starting Member

14 Posts

Posted - 2006-06-16 : 09:30:04

Perfect!!
Thanks Guys.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-19 : 13:33:56

What about:

SELECT		COUNT(*) 'Total',
		SUM(CASE WHEN PhoneNum LIKE '01%25' THEN 1 ELSE 0 END) 'Subset'
FROM		dbo.CallRslt 
INNER JOIN	Mcalcode ON Mcalcode.CallResultCode = dbo.CallRslt.CallResultCode
WHERE		Mcalcode.Presentation = 1
		AND DateofCall >= dateadd(d, datediff(d, 0, GETDATE()), -1)
                AND DateofCall < dateadd(d, datediff(d, 0, GETDATE()), 0)

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources