Assist with Partition...and Over

Transact-SQL
#1

Hi all

my data looks like this, it is about logon sessions.

Period Customer Company distinct(Period,Customer,Company) what I want
201801 x 15 1 1
201801 x 15 1
201801 x 15 1

201801 y 15 1 1

201803 z 15 1 1

so I'm using qlikView, but my question is SQL related

in effect what I need is to count Distinct (Period, Customer, Company) so even if he logged on 3 times (like x in January) I only want '1' returned.

Any help will be appreciated

Thanks

#2

Are you looking for this

count(*) over(partition by period,customer,company)
#3

Hello thank you for your reply.

what does that statement exactly mean? will it count every combination of period, customer and company?

Thank you

#4

If your data are:

Period Customer Company
201801 x        15
201801 x        15
201801 x        15
201801 y        15
201803 z        15

You'll get:

Period Customer Company Count
201801 x        15      3
201801 x        15      3
201801 x        15      3
201801 y        15      1
201803 z        15      1

But why don't you just try it and see what happens? It'll take you less than 1 min you paste the above into your query.

#5

hi

I get an error message on the select. Here is my statement

select count (*) over (partition by round(logon_date/100,0),customer,company)
from totus_fct.fct_dbanking_logon_dly
where channel = '1BB'
and co_cde = 15

the error message says error near '*'

#6

Which version of Microsoft SQL Server are you using?
Whats the complete error message?

#7

hi,

We are on MySql - Workbench 6.3. Dont know if that helps

#8

I see.
This site is dedicated to Microsoft SQL Server, so (of course) you'll get solution for that.

You probably will get much better help on a MySQL forum.