A Group by with a where clause ??

Question

Post reply

A Group by with a where clause ??

jbalbo

SSCertifiable

Points: 7583
More actions
September 12, 2016 at 2:49 pm

#330113

Hi
I am using the code below to group by client and score
I need to not count if the field dbo.[BPRS Scores].Score = 33
Thanks in advance
SELECT TOP (100) PERCENT SUM(dbo.[BPRS Scores].Score)-COUNT(dbo.[BPRS Scores].Score) AS Expr1, dbo.e_ClientCases.Client, dbo.BPRS.ObjectID, dbo.e_CalendarEvents.StartTime
,ROW_NUMBER() over (Partition by edata.dbo.e_ClientCases.Client order by dbo.e_CalendarEvents.StartTime)
FROM dbo.BPRS INNER JOIN
dbo.[BPRS Scores] ON dbo.BPRS.ObjectID = dbo.[BPRS Scores].Parent INNER JOIN
dbo.e_ObjectMetadata ON dbo.BPRS.ObjectID = dbo.e_ObjectMetadata.ObjectID INNER JOIN
dbo.e_ClientCases ON dbo.e_ObjectMetadata.[Case] = dbo.e_ClientCases.ObjectID LEFT OUTER JOIN
dbo.e_CalendarEvents ON dbo.BPRS.ObjectID = dbo.e_CalendarEvents.ParentObject
GROUP BY dbo.BPRS.ObjectID, dbo.e_ClientCases.Client, dbo.e_CalendarEvents.StartTime
ORDER BY dbo.e_ClientCases.Client

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jbalbo SSCertifiable Points: 7583 More actions · Answer 1

I'm sorry....

I think I just need an And statement

"and dbo.[BPRS Scores].Score <> 33"

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Not an AND (since there's nothing to add the AND to, just a WHERE clause.

SELECT SUM(dbo.[BPRS Scores].Score) - COUNT(dbo.[BPRS Scores].Score) AS Expr1,

dbo.e_ClientCases.Client,

dbo.BPRS.ObjectID,

dbo.e_CalendarEvents.StartTime,

ROW_NUMBER() OVER (PARTITION BY edata.dbo.e_ClientCases.Client ORDER BY dbo.e_CalendarEvents.StartTime)

FROM dbo.BPRS

INNER JOIN dbo.[BPRS Scores] ON dbo.BPRS.ObjectID = dbo.[BPRS Scores].Parent

INNER JOIN dbo.e_ObjectMetadata ON dbo.BPRS.ObjectID = dbo.e_ObjectMetadata.ObjectID

INNER JOIN dbo.e_ClientCases ON dbo.e_ObjectMetadata.[Case] = dbo.e_ClientCases.ObjectID

LEFT OUTER JOIN dbo.e_CalendarEvents ON dbo.BPRS.ObjectID = dbo.e_CalendarEvents.ParentObject

WHERE dbo.[BPRS Scores].Score <> 33

GROUP BY dbo.BPRS.ObjectID,

dbo.e_ClientCases.Client,

dbo.e_CalendarEvents.StartTime

ORDER BY dbo.e_ClientCases.Client;

Should work, though obviously not tested.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jbalbo SSCertifiable Points: 7583 More actions · Answer 3

Thanks Gail,

This does work.. can I add something to this....

Row_number gives me the count of the number of clients records based on the Starttime

Is there a way to get the total number of records per client?

For example my output may look like

6096886728425202016-05-19 00:00:00.0001

3396888321981052016-01-28 00:00:00.0001

3396888334854262016-08-29 00:00:00.0002

4496892221981372016-01-28 00:00:00.0001

5896896321980452016-01-28 00:00:00.0001

4196901624330642016-03-09 00:00:00.0001

5196901631976342016-07-18 00:00:00.0002

I would want to add another column with count by client number

Expr1Client ObjectIDStartTime row COUNTCLIENT

6096886728425202016-05-19 00:00:00.0001 1

3396888321981052016-01-28 00:00:00.0001 2

3396888334854262016-08-29 00:00:00.0002 2

4496892221981372016-01-28 00:00:00.0001 1

5896896321980452016-01-28 00:00:00.0001 1

4196901624330642016-03-09 00:00:00.0001 2

5196901631976342016-07-18 00:00:00.0002 2

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Try

COUNT(*) OVER (PARTITION BY edata.dbo.e_ClientCases.Client)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jbalbo SSCertifiable Points: 7583 More actions · Answer 5

Thanks Gail...

Any it's always a good thing when you learn something new !!!!

Thanks Again