Group By

[Khanson82]

Is there a way to use this below top part, but not use any of the fields in the group by other than:a.Zip,a.clientid,a.state,a.agent?
By adding the other fields in the group by, it is messing up the results. But since those fields are in my case statements, it makes me use them in the group by line..Thanks..


(SELECT
/**************************/
case
when a.clientid ='9' then a.agency
else b.clientname
end as Client,
/**************************/
a.state,
a.agent as County,
a.Zip,
/**************************/
case
when a.status = 'initial call' then COUNT(a.Zip)
else '0'
end as PICMembers,
/**************************/
case
when a.status in ('member','pending scheduling','provider','reopen','scheduling','special handling') then COUNT(a.Zip)
else '0'
end as NYSMembers,
/**************************/
COUNT(a.Zip) as TotalMembers

FROM dbo.vwapp0 AS a WITH (nolock) INNER JOIN
dbo.vwaccount AS b WITH (nolock) ON a.clientID = b.ClientID
WHERE (a.StatusReason IN ('pending', 'reopen', 'reschedule', 'spanish pending', 'appointment issue')) AND (a.FuDt <= DATEADD(dd, 1, GETDATE()) OR
a.FuDt IS NULL)
GROUP BY a.Zip,a.clientid,a.state,a.agent,a.agency,b.clientname,a.status,a.state,a.agent,a.statusreason,a.status)

 

[bborissov]

Sure,
Just round the fields you don;t need in GROUP BY with MAX() function.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Khanson82]

So would I do max(field) in the group by line or in the statement for every time the additonal field is needed? Thanks again.

 

[markros]

In your select statement where you specify the fields you're selecting. If the field is not in the GROUP BY clause, it needs to be wrapped in the MAX() function.

PluralSight Learning Library

 

[bborissov]

No.
The MAX() is aggregate function and there is no need to put it in the GROUP BY clause.
That way you will get the max values of the fields you didn't want to be in GROUP BY clause.
It seems you didn't care what exactly value you will get or them, so the MAX() is as good as MIN().

But if you provide some example data and desired result it will be easier for me to understand what you want.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Khanson82]

Thank you both. I believe after a little editing, it is working!