CODEWITHSUNDEEP
sqlsql-server-2008
user3591637
user3591637

Reputation: 499

How to output results by order of year and count of records?

I'm trying to write a query that gives me the count of records where execoffice_status=1 (could equal =0 too). I want to output the results by using a different table employee which gives me their names.

The query I wrote seems to give me some results but gives me all the records in the table even where execoffice_status=0 (not sure how I would add that to the query). What I'm trying to get out off the query is the count of records that execoffice_status=1 and from what year (execoffice_date), what eventually i would like from the query is the top 10 from each year (order by year).

With the query below I get all the record and even where execoffice_status=0 query:

SELECT * 
FROM (
    select      ROW_NUMBER() OVER(PARTITION BY e.emp_namelast order by year(c.execoffice_date) desc ) as RowNum,
                year(c.execoffice_date) as year, e.emp_nameFirst + ' ' +  e.emp_namelast as fullname, count(c.execoffice_status) as stars

    from        phonelist.dbo.employee e 
    join        intranet.dbo.CSEReduxResponses c on c.employee = e.emp_id
    group by    emp_namelast, emp_namefirst, year(c.execoffice_date)

    ) a

order by year

Here is a http://sqlfiddle.com/#!3/79f253/1 that I made with some dummy data.

Upvotes: 1

Views: 51

Answers (3)

user3726459
user3726459

Reputation: 182

Here is how I understood your requirements.

Get count of records for each employee per year where execoffice_status is 1 execoffice_status can be one or zero

in this case, you can use sum and group by, if execoffice_status can be another number other than one or zero, then we would need to use rownum and count, instead of sum and group by

let me know if this does what you want.

select * from(
select a.employeEe,a.execoffice_date, SUM(a.execoffice_status) execoffice_status_count
from CSEReduxResponses a
group by a.employeEe,execoffice_date
  ) a
left outer join employee b
on b.emp_id = a.employeee
where EXECOFFICE_STATUS_COUNT > 0
order by execoffice_date desc;

also if you want to get the top 10 rows, I think with sql server you do Select TOP 10 field1, field2, field3 from table

Upvotes: 0

crthompson
crthompson

Reputation: 15865

For the first bit of your question you can simply add a where clause.

where       c.execoffice_status=1

To get the top values for each year, Rank can accomplish this:

SELECT * 
FROM (
    select      RANK() OVER(PARTITION BY year(c.execoffice_date) order by e.emp_namelast desc ) as Rank,
                year(c.execoffice_date) as year, e.emp_nameFirst + ' ' +  e.emp_namelast as lastName, sum(c.execoffice_status) as stars

    from        employee e 
    join        CSEReduxResponses c on c.employeee = e.emp_id
    where       c.execoffice_status=1 
    group by    emp_namelast, emp_namefirst, year(c.execoffice_date)

    ) a
where rank <= 2
order by year

fiddle

This numbers the users by their stars and gives you the top 2 for each year. (for 10 just <= 10)

Upvotes: 2

TMNT2014
TMNT2014

Reputation: 2130

SELECT * FROM (
select      ROW_NUMBER() OVER(PARTITION BY e.emp_namelast order by year(c.execoffice_date) desc ) as RowNum,
            year(c.execoffice_date) as year, e.emp_nameFirst + ' ' +  e.emp_namelast as lastName, sum(c.execoffice_status) as stars

from        employee e 
join        CSEReduxResponses c on c.employeee = e.emp_id
where       c.execoffice_status <> 0
group by    emp_namelast, emp_namefirst, year(c.execoffice_date)

) a WHERE RowNum<=10  order by year

Upvotes: 0

Related Questions