Sajid Hussain

Sajid Hussain

  • 1.2k
  • 514
  • 99.9k

c# query to group same values from one column based on other

Nov 21 2014 1:01 AM
i have drop down in the form,and an event is fire when any value is selected.
in the database have a table there are two column one is link with drop down against each value of this there are multiple value in the 2nd column, how can i count and display these value in the textboxes of the form.
<div class="form-group">             <label class="col-sm-2 control-label">                     Select Sector:                 </label>                 <div class="col-sm-3">                    <asp:DropDownList CssClass="form-control" runat="server" MaxLength="7" ID="ddlToSector" OnSelectedIndexChanged="ddlToSector_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList>                      </div>             <label class="col-sm-2 control-label">                 Strenght:             </label>             <div class="col-sm-1">                 <asp:TextBox runat="server" ID="txtStrenght" AutoPostBack="true" ></asp:TextBox>             </div>         </div>           <div class="form-group">             <label class="col-sm-2 control-label">                 OnLeave:             </label>             <div class="col-sm-3">                 <asp:TextBox runat="server" ID="txtOnLeave" AutoPostBack="true" ></asp:TextBox>             </div>             <label class="col-sm-2 control-label">                 Absent:             </label>             <div class="col-sm-3">                 <asp:TextBox runat="server" ID="txtAbsent" AutoPostBack="true"></asp:TextBox>             </div>         </div>
protected void ddlToSector_SelectedIndexChanged(object sender, EventArgs e) {     var sectorStrength = _service.GetAllEmployeeDuty().Where(x =>          x.ToSector_Id == SafeConvert.ToInt32(ddlToSector.SelectedValue));     txtStrenght.Text = sectorStrength.ToString();       var sectorAbsent = _service.GetAllEmployeeAbsent().Where(x =>          x.Sector_Id ==SafeConvert.ToInt32(ddlToSector.SelectedValue)).Count();     txtAbsent.Text = sectorAbsent.ToString();       var empStatus = _service.GetAllEmployeeStatus().Where(x =>          x.Rank_Id == SafeConvert.ToByte(ddlToSector.SelectedValue)).Count();     txtSpecialDuty.Text = empStatus.ToString();
First sectorStrenght work fine ,it return exact value from the table
problem is in EmployeeStatus table in which i have different value against same Sector Id,
i want that when any sector is selected query will check in this table againt Sector ID DIFFERENT status of employee then count it and return in to text box
Here is EmployeeStatusType table. USE [CTPHR] GO   /****** Object:  Table [dbo].[EmployeeStatusType_Id]    Script Date: 11/21/2014 10:03:15 AM ******/ SET ANSI_NULLS ON GO   SET QUOTED_IDENTIFIER ON GO   SET ANSI_PADDING ON GO   CREATE TABLE [dbo].[EmployeeStatusType_Id]( 	[Id] [tinyint] IDENTITY(1,1) NOT NULL, 	[Name] [varchar](25) NOT NULL, 	[IsDutable] [bit] NOT NULL,  CONSTRAINT [PK_EmployeeStatusType_Id] PRIMARY KEY CLUSTERED  ( 	[Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   GO   SET ANSI_PADDING OFF GO   this is 2nt table against sector id there are different employeestatustype ,this sector id is used to fire an event . USE [CTPHR] GO   /****** Object:  Table [dbo].[EmployeeStatus]    Script Date: 11/21/2014 10:06:35 AM ******/ SET ANSI_NULLS ON GO   SET QUOTED_IDENTIFIER ON GO   SET ANSI_PADDING ON GO   CREATE TABLE [dbo].[EmployeeStatus]( 	[Id] [tinyint] IDENTITY(1,1) NOT NULL, 	[Employee_Id] [varchar](8) NOT NULL, 	[EmpstatusType_Id] [tinyint] NOT NULL, 	[StartDate] [date] NOT NULL, 	[EndDate] [date] NOT NULL, 	[Sector_Id] [smallint] NOT NULL,  CONSTRAINT [PK_Mp_status] PRIMARY KEY CLUSTERED  ( 	[Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   GO   SET ANSI_PADDING OFF GO   ALTER TABLE [dbo].[EmployeeStatus]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeStatus_EmployeeStatus] FOREIGN KEY([Id]) REFERENCES [dbo].[EmployeeStatus] ([Id]) GO   ALTER TABLE [dbo].[EmployeeStatus] CHECK CONSTRAINT [FK_EmployeeStatus_EmployeeStatus] GO