Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to get count of combination for multiple rows Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 12:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 8:59 PM
Points: 1, Visits: 5
Hi
My application need at least 2 approval for an request.

I need to generate a report to count which combinations are the most.

example

ApplicationID DateTime ApprovalUserID
1 10/10/2012 1
1 10/10/2012 2
1 10/10/2012 3
2 11/10/2012 1
2 11/10/2012 3
3 12/10/2012 2
3 12/10/2012 3
4 13/10/2012 1
4 13/10/2012 2
5 14/10/2012 1
5 14/10/2012 3
6 15/10/2012 1
6 15/10/2012 3
7 16/10/2012 1
7 16/10/2012 2

My report should show
Approval1 Approval2 Approval3 Count
1 2 3 1
1 2 2
1 3 3
2 3 1

I manage to the the result like above but without the COUNT.
I really no idea how to make script work. Please help.
Post #1375267
Posted Monday, October 22, 2012 9:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 12,027, Visits: 11,055
wailoon.ho (10/22/2012)
Hi
My application need at least 2 approval for an request.

I need to generate a report to count which combinations are the most.

example

ApplicationID DateTime ApprovalUserID
1 10/10/2012 1
1 10/10/2012 2
1 10/10/2012 3
2 11/10/2012 1
2 11/10/2012 3
3 12/10/2012 2
3 12/10/2012 3
4 13/10/2012 1
4 13/10/2012 2
5 14/10/2012 1
5 14/10/2012 3
6 15/10/2012 1
6 15/10/2012 3
7 16/10/2012 1
7 16/10/2012 2

My report should show
Approval1 Approval2 Approval3 Count
1 2 3 1
1 2 2
1 3 3
2 3 1

I manage to the the result like above but without the COUNT.
I really no idea how to make script work. Please help.


I am not really sure what you want here. Can you post ddl and sample data along with the desired output. Take a look at the first link in my signature about best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1375538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse