llouw1
June 15, 2018, 12:31pm
#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
Are you looking for this
count(*) over(partition by period,customer,company)
llouw1
June 17, 2018, 11:07am
#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
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.
llouw1
June 18, 2018, 5:22am
#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 '*'
Which version of Microsoft SQL Server are you using?
Whats the complete error message?
llouw1
June 19, 2018, 5:04am
#7
llouw1:
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
hi,
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.