count iif

Hope someone can help.
I am trying to do a count where a field is equal to a certain number of
values.
The example below doesn't work but this is the idea.
=Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
Fields!JobNo.Value, Nothing))
I wondered if someone could point me in the right direction.
Thanks PaulSELECT SUM(CASE WHEN VisitType IN ('RTF', 'ARF', 'ERF', 'WOK') THEN 1 ELSE 0
END)
FROM your_table
--
Jacco Schalkwijk
SQL Server MVP
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:6D921197-A432-4C19-B5AE-1958B94CE795@.microsoft.com...
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Try the posted case statement first, but if that doesn't work, you have to
spell out your fields again.
Like:
=Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
Fields!JobNo.Value, Nothing))
Hope that helps!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"pcalv" wrote:
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Thanks for the replies.
Catadmin i used your suggestion which worked great.
Cheers Paul
"Catadmin" wrote:
> Try the posted case statement first, but if that doesn't work, you have to
> spell out your fields again.
> Like:
> =Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
> or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
> Fields!JobNo.Value, Nothing))
> Hope that helps!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "pcalv" wrote:
> > Hope someone can help.
> >
> > I am trying to do a count where a field is equal to a certain number of
> > values.
> >
> > The example below doesn't work but this is the idea.
> >
> > =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> > Fields!JobNo.Value, Nothing))
> >
> > I wondered if someone could point me in the right direction.
> >
> > Thanks Paul|||Glad I could help. @.=)
Catadmin
"pcalv" wrote:
> Thanks for the replies.
> Catadmin i used your suggestion which worked great.
> Cheers Paul
>

COUNT Expression Values

Hello,

I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.

Any info is helpful.

I answered my own question. I didn't need any coding. It was basically creating new fields and applying the calculations to the new created fields.

COUNT Expression Values

Hello,

I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.

Any info is helpful.

I answered my own question. I didn't need any coding. It was basically creating new fields and applying the calculations to the new created fields.sql

Count

Hi,

I need to write an SQL query/code to count say E's from a certain table. I am working in SQL Server 2005 (sp2).

For example, consider the table below;

ID From 12 D 12 D 12 C 12 E 12 D 13 D 13 E 13 D 13 E 13 D 14 H 14 D 14 D 14 D 14 D

After the SQL code, I need a result like this table below: In otherwords, I wish to have E's counted if theres E else 0 for that ID.

ID Count E's 12 1 13 2 14 0

Thanks in advance.

try this

SELECT a.[ID]
, COUNT(b.[From]) AS [Count E]
FROM YourTable a LEFT OUTER JOIN
(
SELECT [ID]
. [From]
FROM YourTable
WHERE [From] = 'E'
) b ON a.[ID] = b.[ID]
GROUP BY
a.[ID]|||

This should work reliably:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( [ID] int,
[From] char(1)
)

INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 12, 'C' )
INSERT INTO @.MyTable VALUES ( 12, 'E' )
INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'E' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'E' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'H' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )

SELECT DISTINCT
m.[ID],
[From] = isnull( dt.[From], 0 )
FROM @.MyTable m
LEFT JOIN ( SELECT
[ID],
[From] = count( [From] )
FROM @.MyTable
WHERE [From] = 'E'
GROUP BY [ID]
) dt
ON m.[ID] = dt.[ID]

ID From
-- --
12 1
13 2
14 0

I highly recommend that you avoid using Reserved words for table/column names. FROM is a HIGHLY reserved word, and can only be used by enclosing in double quotes or square brackets.

|||LOL, i haven't tested my post. arnie's post is correct.|||Depending on the distribution of data and what indexes there are on the table, this may be a better solution:

Code Snippet

SELECT
ID,
SUM(

CASE WHEN [From] = 'E'

THEN 1 ELSE 0 END

) AS [Count E's]
FROM @.MyTable
GROUP BY ID

Steve Kass
Drew University
http://www.stevekass.com
|||

You can also use "GROUP BY ALL"

SELECT
[ID],
COUNT(*) AS [Count E's]
FROM @.MyTable
WHERE [From] = 'E'
GROUP BY ALL [ID]

|||

Try using a CASE expression.

select [ID], sum(case when [From] = 'E' then 1 else 0 end) as [Count E's]

from dbo.t1

group [ID]

go

AMB

|||

Thank all.

I tried all the replies and the one that well with my data was Steve's post.

Cheers