.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Group by department with a select top 1 and average rating!?

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server



I'm trying to create a report that can use the same data set to group by a particular field - e.g. Department or Job title, and then output by each the most commonly occurring piece of data for the selected group.  Plus I want an average as well.

So for example I have:

Job Title, Department, Chosen Factor, Rating

Programmer, IT, 'factor x', 7

Programmer, IT ' factor x', 8

Sales Manager, Sale, factor z, 9


From the one dataset I want to create two tablixs one that reports:

Job Title, Most common selected factor, avg rating

Programmer, 'factor x', 7

Sales Manager, 'factor z, 8


And then another tablix that shows

Department, Most common selected factor, avg rating

IT, 'factor x', 7

Sale, 'factor z, 8


Now depending on whether I'm grouping on Department or Job title the number of any particluar factor will change, as will the average rating.


Can I do this?  Or do I need a seperate dataset for each table?


Thanks in advance

View Complete Post

More Related Resource Links

Problem Add Category Item by select department

Add Category Item by select department Firstly database has two tables Department and Category Department Table Id -------int  (PK) dep_name--------nvarchar(50) dep_description---------nvarchar(100) -------------------------------------------------------- Category Table Id------int cat_name------- nvarchar(50) cat_description----------- nvarchar(100) IsActive--------bit DepartmentId-----int   (FK) ---------------------------------------------------------- I have form contain DetailsView control under name CategoryDetailsView. My problem: this mechanism not duo correctly I don't know why,  only show to me when I run page" Procedure or function InsertCategory has too many arguments specified. " My idea when a user need to create category must on him write category details ( category name, Description, IsActive) and select this category where to insert by select department name from drop downlist This control connecting with table under name Category, insert process by stored procedure under name sp_InsertCategoryALTER Procedure [dbo].[InsertCategory] (@cat_name nvarchar(50) ,@cat_description nvarchar(100),@IsActive bit , @Result int output)AsBegin Insert Into Category (cat_name , cat_description, IsActive ) Values (@cat_name , @cat_description, @IsActive ) Select @Result = @@IdentityEnd <asp:DetailsView ID="CategoryDetailsView

How to do a <> Select Query, and assign results to a Group 'Other'


How can I use this in a Select Query?
<> "*" & "Internet" & "*" Or <> "*" & "Old Customer" & "*" Or <> "*" & "Reference" & "*" Or <> "*" & "Saw Trucks" & "*" Or <> "*" & "Y/P" & "*" I want to group all the results (named count) and call the result 'Other'

Here’s my SQL now:

SELECT DATABASE.[LEAD FROM], Count(DATABASE.[LEAD FROM]) AS [Count of Leads], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-7,Date()) & "#") AS [Last 7-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-30,Date()) & "#") AS [Last 30-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-365,Date()) & "#") AS [Last 365-Days]



How to get group count and total count using one select statement?


How to obtain the resultset using one select statement?


Table 1







Select statement with two group level to create addtional field.


Here is an example of my question

I have  a column called Department, a column called Employee Name and a column called EmployeeAbsent.

Obviously i can easily use a select satement to get each employee's absent status. However, I would like to generate an additional field called DepartmentAbsent to see if anyone in a particular depart is absent based on the EmployeeAbset field.

how should I write the query?

How can I select a group of objects in a canvas?


Hi together,

I have several Rectangle Objects in a Canvas element. A style is defined for some mouse over effects and I can check, if one of this rectangles has been clicked.

Now I want to implement a "selection tool": Press the left mouse button down and drag a rectangular "selection". After that, I want to know which of the rectangles are within these selection ...

I found some "HitTests", but these seems only work with a single Point (mouse click) not with an area?



AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

Problem to group radio button across gridview rows

In general no one can easily group or make a single selection from radio button list inside gridview rows. There are a lot of way to make a single or unique selection from list. Here i want to share how i can address this problem in the most easiest way. Googling the problem most of the cases i saw the grouping in horizontal way. Thats why i tried to group the radio buttons in vertical manner. Let i have a requirement like below:

Group Calendars in SharePoint 2010

One of the many requests I had in SharePoint 2007 was the ability to place SharePoint Calendars on top of each other like you can in Outlook 2007 saving you having to look at each and work out when the best time is for a meeting.

ListViewControl - how to group items by their value, for eg. date range



I have a table containing courses, columns are 

Name - "Yoga Classes"

DateStart - "2/23/2010"

DateEnd - "2/24/2010"

I am using ListView Control to bind it, is there a way to display them and group them by Month? 

for eg.


Piano Class (10th Jan 2010 - 11th Jan 2010)
Guitar Class (10th Jan 2010 - 11th Jan 2010)


Yoga Class (15th Feb 2010 - 16th Feb 2010)

Thanks for help.

Select value in gridview


Hi, I am new to gridview. I want to make a gridview. Its row when selected should direct me to a specific .aspx page.Each row should direct to a different page. Should I use querystrings for this? Please suggest. Regards, ap.

How do I get the emails of all the members of a given group in ActiveDirectory - Need help



  I have a need to send out emails to all the people who are in a given group say "HR Department". I need to be able to get all the emails through a for loop and then as it retrieves I should send emails to them. Can some one please help me with the code

this is what I came up with but not sure how to go about writing a for loop to get all the emails in the given group.

public static string SendEmails(string loginName)
        string Department = "HR Department"
        string userName = ExtractUserName(loginName);
        DirectorySearcher search = new DirectorySearcher();
        search.Filter = String.Format("(SAMAccountName={0})", userName);
        //search.Filter = String.Format("(SAMAccountName={FAY ImprovmentForm})", userName);
        //LdapSearcher.Filter = string.Format("(&(objectClass=user)(department={0}))", Department); 
        //search.Filter = string.Format("(&(objectClass=group)(SAMAccountName=" + Department + "))", userName);

Need Help in LINQ query for group By with chunks of record


I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&

Test Run: Group Determination In Software Testing


There is a large body of research on group techniques to determine the best alternative from a set of options. Dr. James McCaffrey outlines five of them.

Dr. James McCaffrey

MSDN Magazine November 2008

Advanced Basics: Creating a Five-Star Rating Control


I have to admit it; most of my Windows® Forms controls are an attempt to copy something that already exists. In my October 2004 column I showed you how to create a progress bar that mimicked the one shown during the Windows XP setup routine, and this month I'm at it again.

Duncan Mackenzie

MSDN Magazine January 2005

The XML Files: XSLT Keys, Select vs. Match, Conflict Resolution, and More


This month Aaron Skonnard covers how keys work in XSLT, XSLT match and select attributes, SelectNodes order, finding