Home > Mobile >  SQL Counting Rows based on break values
SQL Counting Rows based on break values

Time:01-01

Frequently I need to do something in SQL Server that I just don't know how to do properly.

Basically, if I have a table #sample that is like this:

DEPARTMENT DATE
ACCOUNTING A
FINANCE A
PAYROLL A
ACCOUNTING B
PAYROLL A
FINANCE A
PAYROLL B
PAYROLL C

I need to query that table to get an output that is this:

DEPARTMENT DATE DUPECHECK
ACCOUNTING A 1
ACCOUNTING B 2
FINANCE A 1
FINANCE A 1
PAYROLL A 1
PAYROLL A 1
PAYROLL B 2
PAYROLL C 3

In Oracle Hyperion Brio at my old job I used to create a column that value 1 for every row and writing a function Cume on that column with a break value of the column I needed to count.

In Excel, I could create a concatenated column that has my break values, and then do a countif(concat:concat,concat1) copy that formula down.

I feel like it should be something similar to this, but that's not it at all:

ROW_NUMBER() OVER (PARTITION BY #sample.Department ORDER BY #sample.Date) DUPECHECK

Help me out?

CodePudding user response:

You can use the dense_rank().

DENSE_RANK() OVER(PARTITION BY #sample.Department ORDER BY #sample.Date)DUPECHECK
  • Related