Incremental counts in mysql -

-

i have table "userlogins" , when user login system drop record in "userlogins" table.

jan 10th (users : 1,2,3,4)   // 4 records ids 1,2,3,4 jan 20th (1,2,3,4,5,6)       // 6 records ids 1,2,3,4,5,6 jan 30th (1,2)  feb 10th (1,7) feb 20th (2,6,8) feb 25th (1,2,3,5)  mar 10th (3,4) mar 20th (4,5,9) mar 30th (8,10,11)  apr 10th (10,12) apr 20th (1,2,3,6,13, 14, 15) apr 30th (11,12,16)

when write group results follows

jan - 6 feb - 7 mar - 7 apr - 11

but need out put follows

upto jan - 6 //count of distinct users upto jan upto feb - 8 //count of distinct users upto feb upto mar - 11 //count of distinct users upto mar upto apr - 16 //count of distinct users upto apr

your first count this:

select   date_format(login_date, '%y-%b') year_month,   count(distinct user_id)   userlogins group   date_format(login_date, '%y-%b')

while count users given mount, use join:

select   date_format(last_day, '%y-%b') year_month,   count(distinct user_id)   (select distinct last_day(login_date) last_day    userlogins) d   inner join   userlogins on userlogins.login_date<=last_day group   date_format(last_day, '%y-%b')

on subquery d return last days of every month has record on userlogins table, join userlogin logged end of month, , count.


Comments

Post a Comment

Popular posts from this blog

html - Firefox flex bug applied to buttons? -

-
i'm trying apply flex button on firefox i'm having problem on chrome doesn't happens. this css: .button { display: inline-flex; align-items: center; } as can see in fiddle, "caret" word moved new line, happens if element <button> tag, <div> works expected. http://codepen.io/anon/pen/xblmgx is firefox bug or there css i'm missing? nevermind, looks "mr firefox" doesn't idea... <button> not implementable (by browsers) in pure css, bit of black box, perspective of css. means don't react in same way e.g. <div> would. this isn't specific flexbox -- e.g. don't render scrollbars if put "overflow:scroll" on button, , don't render table if put "display:table" on it. stepping further, isn't specific <button> . consider <fieldset> , <table> have special rendering behavior: data:text/html,<fieldset style="d
Read more

html - Missing border-right in select on Firefox -

-
Image
when put select div width , padding-left, options have no border on right. more, happend if select have width 118px , parent have 15px of padding left! have idea, going on? my code: <!doctype html> <html> <head lang="en"> </head> <body> <div style="width: 1000px; padding-left: 15px;"> <select style="width: 118px;"> <option>a</option> <option>b</option> <option>c</option> </select> </div> </body> </html> and result: here plunker: click it happend on firefox on windows. tested on 40.0.2 version -webkit-box-sizing: border-box; -moz-box-sizing: border-box; -ms-box-sizing: border-box; box-sizing: border-box; source: https://stackoverflow.com/a/11390432/4431269 source of problem: https://bugzilla.mozilla.org/show_bug.cgi?id=924068 and use of google ! edit: (source of problem, explained) after rea
Read more

c# - two queries in same method -

-
i trying execute 2 queries in same method gives me exception. can red of exception declaring new command there way use same command? string id="hi"; connection.open(); oledbcommand command1 = new oledbcommand(); command1.connection = connection; string query1 = "select * products category='" + combobox1.text + "' , subcategory = '" + combobox2.text + "' , sizes='" + combobox3.text + "'"; command1.commandtext = query1; oledbdatareader reader = command1.executereader(); while (reader.read()) { id = reader[0].tostring(); } textbox1.text = id; string query = "insert category_in (category_id,amount,amount_in) values ('"+ id+"' ,500,300)"; command1.commandtext = query; command1.executenonquery(); messagebox.show("saved"); connection.close();
Read more