COUNT multiple values from one column in the database and then split into different columns in my table

user_11432371 0
Viewed 124

I am having trouble trying to figure out how to count multiple values from one column in my SQL Database and then show results in my DataGrid Table.

Here is my Table

enter image description here

<DataGrid.Columns>
  <DataGridTextColumn Header="User" Binding="{Binding UserName}" Width="200"/>
  <DataGridTextColumn Header="Jobs Booked" Binding="{Binding JobsCount}" Width="200"/>
  <DataGridTextColumn Header="Plumbing" Binding="{Binding PlumbingCount}" Width="200"/>
  <DataGridTextColumn Header="Drainage" Binding="{Binding DrainsCount}" Width="200"/>
</DataGrid.Columns>

Here is my current query trying to show plumbing count

    string query = @"
SELECT
  COUNT(ID) AS JobsCount,
  UserName,
  COUNT(JobType) As PlumbingCount
FROM tblJobs
WHERE JobType = 'Plumbing'
 AND CAST(CreatedOn AS DATE) = CAST(GETDATE() AS DATE)
Group by UserName
ORDER BY COUNT(ID) DESC
";

In my database, Total is;

Dan - Username

4 - Total Jobs Booked

3 - Plumbing

1 - Drainage

My current query is only showing total jobs for plumbing.

I need table to show the relevant count based on if its plumbing or drainage which show in same column in my database under 'JobType' and then total jobs for that user.

sql c#
2 Answers

You can use a PIVOT. The PIVOT clause creates named columns from expected data within a given SELECT structure. This has the same output of a simple cross-tab query, but with some flexibility to do even more detailed analysis.

It looks like you need to use a PIVOT like this:

SELECT username, 
jobtotal = SUM([plumbing]+[drainage]), 
[plumbing], 
[drainage] 
FROM
 (
   SELECT 
   username, 
   1 AS jobcount,
   jobtype
   FROM jobs
   WHERE createdon >= CAST(CAST(GETDATE() AS date) AS datetime) 
 AND created < CAST(DATEADD(day, 1, CAST(GETDATE() AS date)) AS datetime)
 ) ps
PIVOT
(
  SUM(jobcount)
  FOR jobtype IN ([plumbing], [drainage])
  ) AS pvt 
 GROUP BY username, [plumbing], [drainage]

http://sqlfiddle.com/#!18/0aef98/8

user_11881970

It looks like you need conditional aggregation here. You do this by placing CASE inside an aggregation.

Further points:

  • COUNT(something) just counts every non-null value, it does not count unique values. For that you need COUNT(DISTINCT something)
  • CAST(CreatedOn AS DATE) = CAST(GETDATE() AS DATE) is not easy for the server to optimize, it's much better to give an explicit begin and end range
SELECT
  UserName,
  COUNT(*) AS JobsCount,
  COUNT(CASE WHEN JobType = 'Plumbing' THEN 1 END) As PlumbingCount,
  COUNT(CASE WHEN JobType = 'Drainage' THEN 1 END) As DrainageCount
FROM tblJobs
WHERE 
 AND CreatedOn >= CAST(CAST(GETDATE() AS date) AS datetime)
 AND CreatedOn < CAST(DATEADD(day, 1, CAST(GETDATE() AS date)) AS datetime)
GROUP BY UserName
ORDER BY COUNT(*) DESC;
user_148689970
Related