logo
down
shadow

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;

Comments
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.
=SUM(IFERROR(1/COUNTIFS(order_id,order_id,referral,"EMP_BC"),0))
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
library(dplyr)
df1%>% 
     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