Assist with Partition...and Over

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


Are you looking for this

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

Hello thank you for your reply.

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

Thank you

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.


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 '*'

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


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

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.