CODEWITHSUNDEEP
sqlsql-servergroup-bycount
John Yaram
John Yaram

Reputation: 3

select multiple columns count one column and group by one column in one table

I have a table named [delivery], with columns [ID],[Employee],[Post],[c_name],[Deli_Date],[note]

I need to Select all columns and count Employee and then group by Employee. data in the table like this:

  ---------------------------------------------------
  | Employee| Post      |c_name  |Deli_Date   |Note |
  |---------|-----------|---------------------|-----|
  |  DAVID  |MANAGER    | a      |11/11/2018  |note |
  |  DAVID  |MANAGER    | b      |01/01/2015  |note |
  |  SAM    |ACOUNTS    | c      |10/10/2016  |note |
  |  DAVID  |IT         | a      |10/02/2015  |note |
  |  DAVID  |DOCTOR     | c      |20/02/2017  |note |
  |  JAMES  |DELIVERYMAN| b      |05/05/2015  |note |
  |  OLIVER |DRIVER     | b      |02/02/2014  |note |
  |  SAM    |ACOUNTS    | c      |02/02/2012  |note |

this code:

select Employee, count(Employee) as TIMES from delivery
group by Employee

the result is: (but I need to show the other columns too).

| Employee| TIMES | 
|---------|-------|
|  DAVID  |   4   |
|  JAMES  |   1   |
|  OLIVER |   1   |
|  SAM    |   2   |

I need my code show Like This:

| Employee| TIMES | Post      |c_name  |Deli_Date   |Note |
|---------|-------|-----------|---------------------|-----|
|  DAVID  |   4   |MANAGER    | a      |11/11/2018  |note |
|  JAMES  |   1   |DELIVERYMAN| b      |05/05/2015  |note |
|  OLIVER |   1   |DRIVER     | b      |02/02/2014  |note |
|  SAM    |   2   |ACOUNTS    | c      |10/10/2016  |note |

what is the best Query could give me that result?

see columns [c_name],[Deli_Date] they shows the last inserted data. or just give me the result without [c_name],[Deli_Date] it's ok.

Upvotes: 0

Views: 9329

Answers (3)

ssamuel
ssamuel

Reputation: 287

select Employee, count(Employee) as TIMES,Post,c_name,Deli_Date,Note from delivery
group by Employee

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want the last date along with the count, you can use window functions:

select d.Employee, d.cnt, d.Post, d.c_name, d.Deli_Date, d.Note 
from (select d.*,
             count(*) over (partition by employee) as cnt,
             row_number() over (partition by employee order by deli_date desc) as seqnum
      from delivery d
     ) d
where seqnum = 1;

Upvotes: 2

The Lyrist
The Lyrist

Reputation: 444

You need to include all the non-aggregated columns in the select and group by clauses

select Employee,
  count(Employee) as TIMES,
  max(Deli_date) as LAST_DELI_DATE,
  post,
  note
from delivery
group by Employee, post, note

Max(Deli_date) will give you the latest date.

To get the latest c_name, note, post, etc. you will need a subquery with a rank function sorted by the deli_date. I will add the example later.

Upvotes: 0

Related Questions