CODEWITHSUNDEEP
sql-server
Muflix
Muflix

Reputation: 6768

mssql distinct count in subquery

I have sql something like

select
name,col2,col3,date
from
table1 join on few tables

And result is

name col2 col3 date
a    a    a    datetime1
a    a    a    datetime1
b    b    b    datetime2
b    b    b    datetime3

and i dont know how to do, but i need to replace date column with column which will shows count of working days for every name/row

I though about subquery like 

,(select COUNT(distinct DATENAME(dw, date) NOT IN ('Saturday', 'Sunday')) where name = '...' from ... where ...) as WorkingDays

but i need help to get it working, thank you.

Upvotes: 0

Views: 208

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460028

Perhaps you want this:

SELECT Name, C2,  
       WorkingDays = Sum(CASE WHEN Datename(DW, [date])IN( 'Saturday', 'Sunday' ) 
                         THEN 0 ELSE 1 END) 
                       OVER (PARTITION BY Name) 
FROM dbo.data

Here is the sql-fiddle with your sample-data: http://sqlfiddle.com/#!3/1279f/14/0

Upvotes: 2

Recursive
Recursive

Reputation: 952

if my understanding is not wrong you just need a groupby of other columns and count of 'date'

   select
        name,col2,col3,count(date) as no_of_days
        from
        table1 join on few tables
        where DATENAME(dw, date) NOT IN ('Saturday', 'Sunday')
        group by name,col2,col3

Upvotes: 1

Related Questions