Runnning total of unique string values in column over days

Runnning total of unique string values in column over days

Content Index :

Runnning total of unique string values in column over days
Tag : sql , By : Bjørn Lyngwa
Date : November 25 2020, 07:22 PM

this one helps. I have an sql table of device logins that looks like: , SQL Server has window functions. So, it would seem that you could do:
select t.*,
       count(distinct devicename) over (partition by date, username) as running_total
from t;
select t.*,
       sum(case when seqnum = 1 then 1 else 0 end) over (partition by date, username) as running_total
from (select t.*,
             row_number() over (partition by date, username, device_name order by (select null)) as seqnum
      from t
     ) t;
select t.*,
       max(seqnum) over (partition by date, username) as running_total
from (select t.*,
             dense_rank() over (partition by date, username order by device_name) as seqnum
      from t
     ) t;

No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Excel: How to count total number of unique values based on a value from a different column

Tag : excel , By : user107506
Date : March 29 2020, 07:55 AM
like below fixes the issue Here is an array formula that should accomplish what you want. To enter an array formula, hold down ctrl-shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula.
Option Explicit
Option Compare Text 'Comment out this line to make case SENSITIVE
Function UniqueOrdersByReferral(rOrderID As Range, rReferral As Range, sReferralCode As String) As Long
    Dim Col As Collection
    Dim I As Long, vOID As Variant, vREF As Variant

vOID = rOrderID
vREF = rReferral

If UBound(vOID) <> UBound(vREF) Then
    MsgBox "Order ID and Referral Ranges must be of same size"
    Exit Function
End If

Set Col = New Collection
On Error Resume Next
For I = 1 To UBound(vOID)
    If vREF(I, 1) = sReferralCode Then Col.Add vOID(I, 1), CStr(vOID(I, 1))
Next I
On Error GoTo 0

UniqueOrdersByReferral = Col.Count

End Function

(Python) How to group unique values in column with total of another column

Tag : python , By : demize95
Date : March 29 2020, 07:55 AM
hop of those help? If the 1s and 0s are boolean flags for each category then you should just need sum.
df[df.country_code == 'USA'].groupby('state_code').sum().reset_index()

#  state_code  commerce  finance  software
#0         CA         0        0         1

Find total number of unique values in a column based on condition in another column

Tag : python , By : Habikki
Date : March 29 2020, 07:55 AM
To fix the issue you can do Use numpy.where with chaining 3 boolean mask by & for bitwise AND:
m = df['ad_type'] == 'sale'
#get groups with values before sale
vals = df.loc[m.groupby(df['sender_id']).cumsum() == 0, 'sender_id'].unique()
m1 = df['sender_id'].isin(vals)
#get last duplicated value per groups - for last sale
m2 = ~df.loc[m, 'sender_id'].duplicated(keep='last').reindex(df.index, fill_value=False)

df['count'] = np.where(m & m1 & m2, 'yes', '')
print (df)
    sender_id  reply_date  ad_type count
0        1234  2016-05-16  sharing      
1        1234  2017-06-20     sale   yes
2        3333  2016-05-16   rental      
3        3333  2016-06-20     sale      
4        3333  2016-06-21     sale   yes
5        6767  2016-05-16     sale      
6         101  2016-04-16     sale      
7         101  2016-04-17     sale      
8        9999  2016-01-01   rental      
9        9999  2017-01-19  sharing      
10       9999  2018-04-17     sale   yes

How do I use the tidyverse packages to get a running total of unique values occurring in a column?

Tag : r , By : Ir0nh1de
Date : March 29 2020, 07:55 AM
I wish this helpful for you I'm trying to use the tidyverse (whatever package is appropriate) to add a column (via mutate()) that is a running total of the unique values that have occurred in the column so far. Here is some toy data, showing the desired output. , Here is an option with group_indices
     mutate(unique_cumsum = group_indices(., n))
#   n unique_cumsum
#1 1             1
#2 1             1
#3 1             1
#4 6             2
#5 7             3
#6 8             4
#7 8             4
df1 <- data.frame("n"=c(1,1,1,6,7,8,8)) 

For unique values in one column get the total count of unique values in another column

Tag : python , By : adrianmooreuk
Date : March 29 2020, 07:55 AM
it should still fix some issue I have two a pyodbc row object that looks like: , It should be something like this:
import collections
import itertools

data = [
    ('Emp1', 'Absent'),
    ('Emp1', 'Absent'),
    ('Emp1', 'Present'),
    ('Emp2', 'Present'),
    ('Emp2', 'Present'),
    ('Emp2', 'Absent'),
    ('Emp2', 'Present'),
    ('Emp2', 'Absent'),
sorted_data = sorted(data, key = lambda x: (x[0], x[1])) # sort our data
employees = collections.defaultdict(dict)
# group by employee
for employee, employee_group in itertools.groupby(sorted_data, lambda item: item[0]):
    # group by category
    for category, category_group in itertools.groupby(employee_group, lambda item: item[1]):
        employees[employee][category] = sum(1 for _ in category_group)

print('employees', employees) # employees defaultdict(<class 'dict'>, {'Emp1': {'Absent': 2, 'Present': 1}, 'Emp2': {'Absent': 2, 'Present': 3}})
Related Posts Related QUESTIONS :
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • Spooling data to CSV truncates
  • How do views reduce code duplication?
  • SQL: Gather right hand values from a join
  • SQL Agent Logon - What is going on?
  • How do I combine two rows of same part, but add quantities?
  • How to optimize this SQL query for a rectangular region?
  • Pass data from ssrs subreport to parent
  • how to enter values in rowguid column?
  • Read multiple tables from dataset in Powershell
  • In SQL how to I exclude a record if there are more than 3 characters after a dash
  • What is the difference between check and foreign key?
  • How to return a record from function, executed by INSERT/UPDATE rule (trigger)?
  • SQL order by oldest from Unix Timestamps
  • How to convert decode function in oracle to standard big query
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com