How Do I Calculate Number Of Row Against Any Id From The Column

Question

0.00/5 (No votes)

See more:

SQL

USE [CTPHR]
GO

/****** Object:  Table [dbo].[EmployeeDuty]    Script Date: 11/12/2014 12:54:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EmployeeDuty](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Employee_Id] [varchar](8) NOT NULL,
	[Rank_Id] [tinyint] NOT NULL,
	[DutyType_Id] [smallint] NOT NULL,
	[AllotedBy_Id] [tinyint] NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NOT NULL,
	[FromSecter_Id] [smallint] NOT NULL,
	[ToSector_Id] [smallint] NOT NULL,
	[UserName] [varchar](15) NOT NULL,
	[EntryDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmplyeeDuty] 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].[EmployeeDuty]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDuty_AllotedBy] FOREIGN KEY([AllotedBy_Id])
REFERENCES [dbo].[AllotedBy] ([Id])
GO

ALTER TABLE [dbo].[EmployeeDuty] CHECK CONSTRAINT [FK_EmployeeDuty_AllotedBy]
GO

ALTER TABLE [dbo].[EmployeeDuty]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDuty_DutyType] FOREIGN KEY([DutyType_Id])
REFERENCES [dbo].[DutyType] ([Id])
GO

ALTER TABLE [dbo].[EmployeeDuty] CHECK CONSTRAINT [FK_EmployeeDuty_DutyType]
GO

ALTER TABLE [dbo].[EmployeeDuty]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDuty_EmployeeDuty] FOREIGN KEY([FromSecter_Id])
REFERENCES [dbo].[Sector] ([Id])
GO

ALTER TABLE [dbo].[EmployeeDuty] CHECK CONSTRAINT [FK_EmployeeDuty_EmployeeDuty]
GO

ALTER TABLE [dbo].[EmployeeDuty]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDuty_Rank] FOREIGN KEY([Rank_Id])
REFERENCES [dbo].[Rank] ([Id])
GO

ALTER TABLE [dbo].[EmployeeDuty] CHECK CONSTRAINT [FK_EmployeeDuty_Rank]
GO

ALTER TABLE [dbo].[EmployeeDuty]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDuty_Sector] FOREIGN KEY([ToSector_Id])
REFERENCES [dbo].[Sector] ([Id])
GO

ALTER TABLE [dbo].[EmployeeDuty] CHECK CONSTRAINT [FK_EmployeeDuty_Sector]
GO

here is my asp code

ASP.NET

<label class="col-sm-2 control-label">
               Total Strenght:
           </label>
           <div class="col-sm-3">
               <asp:TextBox runat="server" ID="txtStrenght" AutoPostBack="true" ></asp:TextBox>
           </div>

here is code behind

C#

protected void check_Click(object sender, EventArgs e)
       {

           var employee = _service.GetAllEmployeeDuty().count();

           txtStrenght.Text = employee.ToString();
             }

it return total no row in the table ,but i want to get total no employee base ToSector Id ,how can i get it,

Posted 11-Nov-14 9:04am

Sajid227

Add a Solution

Comments

Tomas Takac 11-Nov-14 15:14pm

You want to count employees by ToSector_Id? i.e. how many employees are there per each ToSector_Id? Is that correct?

Sajid227 11-Nov-14 15:16pm

YES i want to calculate exactly

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Answer 1 · 2014-11-11T12:25:00

What I understand is you want to count employees by ToSector_Id. You can do that either on database level using SQL. For that you would need to alter your service.

SQL

SELECT [ToSector_Id], Count([Employee_Id]) Employee_Count FROM EmployeeDuty

Alternatively you can use your existing GetAllEmployeeDuty() function with LINQ. Assuming the objects returned from the method map 1:1 to the database tables, this will return list of anonymous objects with the information you want:

C#

var employeeCounts = _service.GetAllEmployeeDuty()
  .GroupBy(x => x.ToSector_Id)
  .Select(x => new { ToSector_Id = x.Key, EmployeeCount = x.Count() });

Edit
The previous LINQ example returns anonymous type. This cannot be returned outside the containing method. You can create your own class however:

C#

public class EmployeeCountByToSectorId
{
  public int ToSectorId { get; set; }
  public int EmployeeCount { get; set; }
}

Then change the LINQ query to use your new class:

C#

var employeeCounts = _service.GetAllEmployeeDuty()
  .GroupBy(x => x.ToSector_Id)
  .Select(x => new EmployeeCountByToSectorId { ToSectorId = x.Key, EmployeeCount = x.Count() });

Please note that using here is fine if your service returns IQueryable. Otherwise I would suggest you run the query on database level and create a new method in your service to return the results.