logo
down
shadow

How to create new column with values counting up every 9th value with SQL?


How to create new column with values counting up every 9th value with SQL?

Content Index :

How to create new column with values counting up every 9th value with SQL?
Tag : sql , By : user178709
Date : January 12 2021, 07:00 PM

I think the issue was by ths following , You can first simply add a column in your table and then update that column with a simple mathematics formula -
UPDATE YOUR_TABLE
SET NEW_COL = CEILING(((ID-1)/9)+1)

Comments
No Comments Right Now !

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

Share : facebook icon twitter icon

Create table with all pairs of values from one column in R, counting unique values


Tag : r , By : Mforg
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further Using data.table and the gtools package, we can recreate all possible permutations by customer:
library(data.table)
library(gtools)

item <- c("h","h","h","j","j")
customer <- c("a","a","b","b","b")
test.data <- data.table(item,customer)

DT <- unique(test.data) #The unique is used as multiple purchases do not count twice

tuples <- function(x){
  return(data.frame(permutations(length(x), 2, x, repeats.allowed = T, set = F), stringsAsFactors = F))
}

DO <- DT[, tuples(item), by = customer]
   customer X1 X2
1:        a  h  h
2:        b  h  h
3:        b  h  j
4:        b  j  h
5:        b  j  j
table(DO$X1,DO$X2)
    j h
  j 1 1
  h 1 2

How can I create query that (counting values inside Column)


Tag : sql-server , By : mux
Date : March 29 2020, 07:55 AM
Any of those help I have 3 companies 1001,1002 ,1003 it could be more and 11 containers with different sizes 1,2,3,4, 5 I want return only the containers that are in the companies that have the same amount or more of specified numbers. for example if I want 2 containers from size 1 and 3 containers from size 2 then only the containers in the company that has 2 or more of size 1 and 3 or more of size 2 should appear let's say that only company 1001 has them then it should appear alone. I tried different queries and post one here but they recommend me to post a new question with the problem that I'm training to make query for. , I think it's this you're looking for:
CREATE TABLE #YourTable(CoID INT,CoName VARCHAR(100),ContainerID INT,Price DECIMAL(10,4),size1 INT,size2 INT,size3 INT,size4 INT,size5 INT);
INSERT INTO #YourTable VALUES
 (6000001,'hbjjvCompany',2000002,50,1,0,0,0,0)
,(6000001,'hbjjvCompany',2000003,50,1,0,0,0,0)
,(6000002,'NCompany',2000004,50,1,0,0,0,0)
,(6000001,'hbjjvCompany',2000005,100,0,1,0,0,0)
,(6000002,'NCompany',2000007,100,0,1,0,0,0)
,(6000001,'hbjjvCompany',2000008,200,0,0,1,0,0)
,(6000001,'hbjjvCompany',2000009,200,0,0,1,0,0)
,(6000001,'hbjjvCompany',2000010,200,0,0,1,0,0)
,(6000002,'NCompany',2000011,200,0,0,1,0,0)
,(6000001,'hbjjvCompany',2000012,400,0,0,0,0,1)
,(6000003,'ghhaCo',2000014,200,0,1,0,0,0);

SELECT CoID
      ,CoName
      ,SUM(Price) AS SumPrice
      ,SUM(size1) AS CountSize1
      ,SUM(size2) AS CountSize2
      ,SUM(size3) AS CountSize3
      ,SUM(size4) AS CountSize4
      ,SUM(size5) AS CountSize5
FROM #YourTable
GROUP BY CoID,CoName;

--Clean up
DROP TABLE #YourTable;
CoID    CoName           SumPrice  s1  s2  s3  s4  s5
6000003 ghhaCo           200.0000   0   1   0   0   0
6000001 hbjjvCompany    1200.0000   2   1   3   0   1
6000002 NCompany         350.0000   1   1   1   0   0

Counting number of unique values in column A based on substring filter on column B


Tag : python-2.7 , By : user86493
Date : March 29 2020, 07:55 AM
it fixes the issue I have a list of 'words' I want to count below
df[word_list]=df.TEXT.apply(lambda x : pd.Series([x.find(y) for y in word_list])).ne(-1)
df1=df[['USER','one','two','three']].set_index('USER').astype(int).replace({0:np.nan})
df1.stack().reset_index().groupby('level_1').USER.agg([lambda x : ','.join(x),len])

Out[31]: 
                        <lambda>  len
level_1                              
one       User 1, User 1, User 3    3
three                     User 2    1
two               User 1, User 2    2
df[word_list]=df.TEXT.str.lower().apply(lambda x : pd.Series([x.find(y) for y in word_list])).ne(-1)
df1=df[['USER','one','two','three']].set_index('USER').astype(int).replace({0:np.nan})
df1.stack().reset_index().groupby('level_1').USER.agg({'User Count':[lambda x : ','.join(set(x))],'Unique':[lambda x : x.nunique()]})


Out[50]: 
          Unique               User Count
        <lambda>                 <lambda>
level_1                                  
one            3   User 2, User 3, User 1
three          1                   User 2
two            2           User 2, User 1
df[word_list]=df.TEXT.str.lower().apply(lambda x : pd.Series([x.find(y) for y in word_list])).ne(-1)
df1=df[['USER','one','two','three']].set_index('USER').astype(int).replace({0:np.nan})
Target=df1.stack().reset_index().groupby('level_1').USER.agg({'User Count':[lambda x : ','.join(set(x))],'Unique':[lambda x : x.nunique()]})
Target.columns=Target.columns.droplevel(1)
Target.drop('User Count',axis=1).reset_index().rename(columns={'level_1':'Words'})
Out[94]: 
   Words  Unique
0    one       3
1  three       1
2    two       2

Counting non empty values in a column, but resetting counter based on an adjacent column


Tag : excel , By : user165781
Date : March 29 2020, 07:55 AM
Any of those help I'm currently trying to write a testing plan for an application with each step needing a 3-part number based on Module, Test, Test Step. , In column C use the following formula
=IF(F18="",C17,0)+1
=IF(E18="",B17+(F18<>""),1)
=COUNTA($E$18:E18)
=A18&"."&B18&"."&C18

Create new column based on counting non-NA values across multiple columns


Tag : r , By : John Tate
Date : March 29 2020, 07:55 AM
should help you out I have a data frame of questionnaire data in a wide format. For some questions, respondents are asked to answer whether a given scenario applies to them (Yes, No). If the scenario applies to them, respondents are asked to provide examples of the scenarios (in some cases, there might be more than one example).
df$column_non_NA= rowSums(!is.na(df[-1]))
df
   Q1  Q1a  Q1b  Q1c column_non_NA
1 Yes  AAA  BBB <NA>             2
2  No <NA> <NA> <NA>             0
3 Yes  AAA <NA> <NA>             1
4  No <NA> <NA> <NA>             0
5 Yes  ABC  BCD  EFG             3
6 Yes  DDD <NA> <NA>             1
7 Yes  EEE  AAA  AAA             3
Related Posts Related QUESTIONS :
  • 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
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • SQL grouping/counting on a string split function
  • ORDER BY and TOP statements in SQL works different on listing down the records and why?
  • SQL Server Computed Column: how to set it to NOT NULL, without persistence
  • Is nested select statement better than where statement?
  • Postgresql sequential scan slow performance on 500 million rows
  • Aggregate first order data to all orders - Microsoft SQL Server
  • Sorting comma delimited datasets in row
  • Question about the correct data structure
  • Showing two temp tables data in a single table
  • How can I find out the query execution time in Oracle SQL using Toad?
  • convert quarter number to month name in sql
  • Find max value from column that has a json object with key-value pairs
  • case statement based on 3 columns in 1 table
  • How to write a SQL query to select batsman wiht highest score than the previous year
  • Find Substring in SQL
  • Using VALUE to make temporary table
  • What is the correct way of writing an exclusive left-join on two tables?
  • Is it possible to run a make table query using a recordset as the source in MS Access (VBA)?
  • INSERT INTO select distinct null PostgreSQL
  • Why converting date from nvarchar(max) to date throws error?
  • how to fix wm_concat in oracle 12c
  • How to UNION a list of tables retrieved from another table with a single query?
  • Oracle query to get a relational dataset result from multimensional table
  • What algorithm is used in a hash partition that it can divide up the data into similar size partitions before seeing any
  • Why does my SQL return unwanted records with my OR in Where clause
  • SQL Multiple Joins Query-Query tuning
  • How to choose max of one column per other column
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com