SQL View to add count column

Transact-SQL
1

I have the following table

Id, Job , Job Date, Process
Sample data
1, JOB01, 01/01/2017, 23
2,JOB01,01/01/2017,12
3,JOB02,01/01/2017,24
4,JOB03,01,10/2017,23

My view should look like this

Job, Date, CountOfProcess,
JOB01, 01/01/2017, 2

You help is appreaciated,
Thanks

2

something like ...

create view as
select Job, JobDate, count(*) as CountOfProcess
from Processes
group by Job , JobDate

I wouldn't use Date as a column name since that's part of the SQL language

3

thanks. Would it also be possible the process as well. Latest from the record line, not sum.

4

this should work:

create view as
select Job, JobDate, count(*) as CountOfProcess,
  (select top 1 Process from Processes P2 
    where P2.Job = Processes.Job and P2.JobDate = Processes.JobDate
    order by P2.ID desc) LatestProcess
from Processes
group by Job, JobDate

... I figured there'd be some kind of group by aggregate function to do this in SQL by now. But I couldn't find one. So if anyone has a better way???

5

If only one result is allowed:

select top(1)
       job
      ,[job date]
      ,process
      ,count(*) over(partition by job) as countofprocess
  from yourtable
 order by countofprocess desc
         ,id desc
;

If multiple result is allowed (in case several jobs have highest count):

select top(1) with ties
       job
      ,[job date]
      ,process
      ,count(*) over(partition by job) as countofprocess
  from yourtable
 order by countofprocess desc
         ,row_number() over(partition by job order by [job date] desc,id desc)
;
6

again I have modified the view here how it displays
image
id process job date
5 1 208 6/3/2015
208 15 208 6/3/2015
7704 1 208 3/8/2017

I need to group to see only one row per date per job
imageid process job date
5 1 208 6/3/2015
7704 1 208 3/8/2017
I don't need to be concerned which process or id i am displaying, in fact I can remove it from the view.

Thanks

7

Maybe this:

select top(1) with ties
       id
      ,process
      ,job
      ,[job date]
  from yourtable
 order by row_number() over(partition by [job date],job order by id)
;
8

If I am reading this correctly - you want one row per job by date and you want to also get the id and process associated with that row as well as a count of the number of processes:

 --==== Test Table
Declare @testTable Table (Id int, Process int, Job int, JobDate date);

 Insert Into @testTable (Id, Process, Job, JobDate)
 Values (5, 1, 208, '2015-06-03')
      , (208, 15, 208, '2015-06-03')
      , (7744, 1, 208, '2017-03-08');

 Select * From @testTable;

 --==== Code
   With jobs
     As (
 Select Id
      , Process
      , Job
      , JobDate
      , row_number() over(Partition By Job, JobDate Order By Process desc) As rowNumber
      , count(*) over(Partition By Job, JobDate) As NumberJobs
   From @testTable
        )
 Select Id
      , Process
      , Job
      , JobDate
      , NumberJobs
   From jobs
  Where rowNumber = 1;

If you want the first row to be returned - remove 'desc' from the order by in the row_number function.

9

Thank you this works great, it lists , however I get an warning message while trying to save this view?
image will this cause problem when querying the table?

10

You have nothing to worry about. But you need to understand it.

When you use the view in an SQL query, if you want the results to be ordered, you must specify the order in that query, rather than relying on the order by inside your view.

11

How have you modified the query? There is no ORDER BY or TOP used in the query other than for the row_number function...

However, if you are using the GUI to create the view instead of just executing a CREATE VIEW statement then maybe the GUI is seeing the row_number order by and popping up a warning.

Note: instead of using the GUI to create a view it is much easier to do that in code:

 Create View dbo.MyView
     As

   With jobs
     As (
 Select Id
      , Process
      , Job
      , JobDate
      , row_number() over(Partition By Job, JobDate Order By Process desc) As rowNumber
      , count(*) over(Partition By Job, JobDate) As NumberJobs
   From @testTable
        )
 Select Id
      , Process
      , Job
      , JobDate
      , NumberJobs
   From jobs
  Where rowNumber = 1;
     Go