Add Column with Order Counts

How to insert a count column into a sql query

With your edit, I see that you want a row ID (normally called row number rather than "count") which is best gathered from a unique ID in the database (person_id or some other unique field). If that isn't possible, you can make one for this report with ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID, in your select statement.

select Name, ROW_NUMBER() OVER (ORDER BY Name DESC) AS ID,
Age, Gender
from customer

This function adds a field to the output called ID (see my tips at the bottom to describe aliases). Since this isn't in the database, it needs a method to determine how it will increment. After the over keyword it orders by Name in descending order.

Information on Counting follows (won't be unique by row):

If each customer has multiple entries but the selected fields are the same for that user and you are counting that user's records (summed in one result record for the user) then you would write:

select Name, count(*), Age, Gender
from customer
group by name, age, gender

This will count (see MSDN) all the user's records as grouped by the name, age and gender (if they match, it's a single record).

However, if you are counting all records so that your whole report has the grand total on every line, then you want:

select Name, (select count(*) from customer) as "count", Age, Gender
from customer

TIP: If you're using something like SSMS to write a query, dragging in columns will put brackets around the columns. This is only necessary if you have spaces in column names, but a DBA will tend to avoid that like the plague. Also, if you need a column header to be something specific, you can use the as keyword like in my first example.

W3Schools has a good tutorial on count()

The COUNT(column_name) function returns
the number of values (NULL values will not be counted) of the
specified column:

SELECT COUNT(column_name) FROM table_name;

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

The COUNT(DISTINCT column_name) function returns the number of
distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name;

COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but
not with Microsoft Access.

SQL - Add Column With Count()

You can use count over:

select
    lag(possession,1) over (order by id) as last_possession,
    possession,
    clock,
    count(*) over (partition by possession) cnt
from plays
where
    game_id in (583615)
    and league = 3
    and period in (0,1)

How to add a column containing sequential counts based on the order of another column?

Try with

df = df.sort_values(['Item ID','Dates'])
df['int'] = df.groupby('Item ID').cumcount()+1

Add column that counts each item

EDIT: Per your request I added answer for SQL Server.

SQL Server:

SELECT 
  name,
  ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) As AddedColumn
FROM YourTable

MYSQL:

SQL Fiddle Demo

SELECT 
    @row_number:=CASE
        WHEN @name = name THEN @row_number + 1
        ELSE 1
    END AS num,
    @name:=name as name
FROM
    YourTable
ORDER BY name;

adding count( ) column on each row

This won't add the count to each row, but one way to get the total count without running a second query is to run your first query using the SQL_CALC_FOUND_ROWS option and then select FOUND_ROWS(). This is sometimes useful if you want to know how many total results there are so you can calculate the page count.

Example:

select SQL_CALC_FOUND_ROWS ID, Title, Author
from yourtable
limit 0, 10;
SELECT FOUND_ROWS();

From the manual:
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows

How to add columns to my data frame, including the counts of another columns , for two different column, in R?

Here is a tidyverse approach. You can group_by your ID column, and count rows that is not NA.

library(tidyverse)

df %>% 
  group_by(ID, l) %>% 
  summarize(n.x = sum(!is.na(x)), n.y = sum(!is.na(y)), .groups = "drop")

# A tibble: 4 x 4
     ID l       n.x   n.y
  <int> <chr> <int> <int>
1     1 s         5     4
2     2 ss        3     2
3     3 m         7     3
4     4 mm        2     2

Add a new column with sum of count to a dataframe according to informations from another in R

Here is one way to do it:

library(dplyr)
library(tidyr)

tab1 %>% 
  mutate(Event_Groups = as.character(Event_Groups)) %>% 
  separate_rows(Event_Groups, sep = ",") %>% 
  left_join(., 
            tab2 %>% 
              unite(col = "Event_Groups", Event, Group) %>% 
              mutate(count = if_else(VALUE1 < 10 & VALUE2 > 30,1L, 0L))) %>%
  group_by(Other_column) %>% 
  summarise(Event_Groups = paste(unique(Event_Groups), collapse = ","),
            Sum_count = sum(count)) %>% 
  select(Event_Groups, everything())

#> Joining, by = "Event_Groups"
#> `summarise()` ungrouping output (override with `.groups` argument)
#> # A tibble: 4 x 3
#>   Event_Groups   Other_column Sum_count
#>   <chr>          <fct>            <int>
#> 1 1_G1,2_G2      A                    1
#> 2 2_G1           B                    0
#> 3 4_G4           C                    1
#> 4 7_G5,8_G5,9_G5 D                    3

^{Created on 2021-07-29 by the reprex package (v0.3.0)}