grouping results for stats

Question

grouping results for stats

jb-1079038

SSC Enthusiast

Points: 173
More actions
June 17, 2009 at 5:09 am

#138793

Hi Folks,
I got some help on a report last week from Barry but now need to expand on that if possible. 😀
The results I get from the query Barry gave me come out like below and are exactly what I wanted for about 3000 records:
UserID Domain Dynamic Mandatory ProfileName
user1 Domain1 MAN2 Profile1
user2 Domain2 DYN1 Profile2
user3 Domain1 DYN1 Profile2
user4 Domain2 DYN1 Profile3
user5 Domain2 DYN2 Profile1
user6 Domain3 MAN1 Profile1
user6 Domain3 MAN1 Proflie3
What I would now like to do with this is come up with stats based on a users dynamic location (how many users have DYN1, DYN2 etc. How many users have MAN1 and How many have both DYN1 and Profile2.
I would like to be able to do this without predefining what DYN1 is as the DYN's can go from 1 to 200, so an average based on WHERE @VAR1=DYN1 would not be workable.
I tried a variation of the code below but got nowhere fast when I realised that I'd have to declare each possibility for DYN 🙁
DECLARE @DYN AS VARCHAR(12),@MAN AS VARCHAR(12),@WSP AS VARCHAR(12)
SET @DYN = 'DYN'
SET @MAN = 'MAN'
SET @WSP = 'PROFILE'
Select
Dept2.Type as 'Dept',
Count(Dept2.Type) as 'Count'
FROM
(SELECT Case
When Dept.Name0 LIKE '%' + @DYN Then 'DYN'
When Dept.Name0 LIKE '%' + @MAN Then 'MAN'
When Dept.Name0 LIKE '%' + @WSP + '%' AND DEPT.VariableValue0 Like '%.Profile%.%' Then 'WSPROFILE'
End as 'Type'--,
FROM v_GS_ENVIRONMENT Dept
WHERE DEPT.Name0 LIKE @DYN
OR DEPT.Name0 LIKE @MAN
OR DEPT.Name0 LIKE @WSP) as Dept2
Group by
DEPT2.Type
ORDER BY
DEPT2.Type
I'm kinda stuck on this and I'm waiting on approval for my T-SQL courses so all I can do right now is footer with this in the hope I can find help here 🙂
I hope what I've posted is clear and that someone might be able to give me some pointers.
Cheers
J

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

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

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

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

jb-1079038 SSC Enthusiast Points: 173 More actions · Answer 2

I don't really understand how to do this. Can someone help in the meantime while I figure it out please?

Cheers

J

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

What is it you don't understand?

I can't really help you out, because I'm not sure what it is that you want exactly. That's why I'm asking for sample data and expected results.

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

jb-1079038 SSC Enthusiast Points: 173 More actions · Answer 4

Well I don't really know any more than the basics of this language so asking me to create tables and sample data based on what looks like a complicated code structure to me is maybe asking a bit much.

All I was asking was if someone could help me out just now while I figure out how to create the sample set (if I can even do that as creating tables in my environment might not be possible due to rights) a bit like the post here:

http://www.sqlservercentral.com/Forums/Topic736607-338-1.aspx

I'm not trying to be awkward but I really am having trouble understanding this right now and until i get my course authorised I'm just losing the will to live!

J

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

For the table definitions, you can generate them from Management studio. Open object explorer, connect to the server, expand out the tree until you get to the tables, right click and chose script.

For the sample data, Jeff's article goes through how to generate insert statements. If you can't work it out, save some data into excel, zip and post the spreadsheet. You don't have to generate any tables in your environment, it's the existing ones that this query will be based on that we're interested in.

I'm not asking for this to be difficult, I'm asking because I'm not sure what it is that you want, and because it's hard (impossible?) to test a query without having data to test on.

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

jb-1079038 SSC Enthusiast Points: 173 More actions · Answer 6

No, I appreciate that your not trying to make it difficult, but it's just being a total n00b makes this very daunting and I'm under a bit of pressure at work, despite the fact they openly admit I've had no training 🙁

Thanks for taking the time to answer, I'll try and get it going the proper way first but failing that I will stick in a csv. and see how I get on from there.

Thanks

J