Count Columns Group By

count columns group by

Although it appears you are not showing all the tables, I can only assume there is another table of actual enrollment per student

select a.Dept, count(*) as TotalStudents
  from students a
  group by a.Dept

If you want the total count of each department associated with every student (which doesn't make sense), you'll probably have to do it like...

select a.Dept, a.Name, b.TotalStudents
    from students a,
        ( select Dept, count(*) TotalStudents
             from students
             group by Dept ) b
    where a.Dept = b.Dept

My interpretation of your "Name" column is the student's name and not that of the actual instructor of the class hence my sub-select / join. Otherwise, like others, just using the COUNT(*) as a third column was all you needed.

How to use count and group by at the same select statement

This will do what you want (list of towns, with the number of users in each):

SELECT `town`, COUNT(`town`)
FROM `user`
GROUP BY `town`;

You can use most aggregate functions when using a GROUP BY statement
(COUNT, MAX, COUNT DISTINCT etc.)

Update:
You can declare a variable for the number of users and save the result there, and then SELECT the value of the variable:

DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM `user`;

SELECT DISTINCT `town`, @numOfUsers FROM `user`;

Get statistics for each group (such as count, mean, etc) using pandas GroupBy?

On groupby object, the agg function can take a list to apply several aggregation methods at once. This should give you the result you need:

df[['col1', 'col2', 'col3', 'col4']].groupby(['col1', 'col2']).agg(['mean', 'count'])

How to COUNT in a specific column after GROUP BY

In statistics, what you are asking for is the mode, the most common value.

You can use aggregation and row_number():

select ct.*
from (select competition_id, item_id, count(*) as cnt,
             row_number() over (partition by competition_id order by count(*) desc) as seqnum
      from t
      group by competition_id, item_id
     ) ci
where seqnum = 1;

In the event that there are ties, this returns only one of the values, arbitrarily. If you want all modes when there are ties use rank() instead of row_number().

Adding a 'count' column to the result of a groupby in pandas?

You can using size

df.groupby(['A','B']).size()
Out[590]: 
A  B
x  p    2
y  q    1
z  r    2
dtype: int64

For your solution adding one of the columns

df.groupby(['A','B']).B.agg('count')
Out[591]: 
A  B
x  p    2
y  q    1
z  r    2
Name: B, dtype: int64

Update :

df.groupby(['A','B']).B.agg('count').to_frame('c').reset_index()

#df.groupby(['A','B']).size().to_frame('c').reset_index()
Out[593]: 
   A  B  c
0  x  p  2
1  y  q  1
2  z  r  2

SQL GROUP BY and COUNT with multiple columns

Use ROW_NUMBER() and COUNT() window functions:

SELECT CASE WHEN t.rn = 1 THEN t.Profile END Profile,
       CASE WHEN t.rn = 1 THEN t.counter END counter,
       t.User_ID,
       t.Name
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY Profile ORDER BY Name) rn,
         COUNT(*) OVER (PARTITION BY Profile) counter
  FROM tablename t
) t
ORDER BY t.Profile, t.rn

I used the column Name to sort the rows in each Profile, but you can use any other column by making the change inside the OVER clause of ROW_NUMBER().

Count the number of NAs in multiple columns after grouping a dataframe in R

I propose two ways:

using dplyr:

df %>% 
  group_by(Region,ID) %>%
  summarise_each(list(na_count = ~sum(is.na(.))))

or data.table:

library(data.table)
setDT(df)[, lapply(.SD, function(x) sum(is.na(x))), by = .(Region, ID)]

Pandas create new column with count from groupby

That's not a new column, that's a new DataFrame:

In [11]: df.groupby(["item", "color"]).count()
Out[11]:
             id
item  color
car   black   2
truck blue    1
      red     2

To get the result you want is to use reset_index:

In [12]: df.groupby(["item", "color"])["id"].count().reset_index(name="count")
Out[12]:
    item  color  count
0    car  black      2
1  truck   blue      1
2  truck    red      2

To get a "new column" you could use transform:

In [13]: df.groupby(["item", "color"])["id"].transform("count")
Out[13]:
0    2
1    2
2    2
3    1
4    2
dtype: int64

I recommend reading the split-apply-combine section of the docs.

How can I group with multiple columns and count?

In order to aggregate the data for a set number of PrintTypes, you can group by SaleId as the additional data like address, phone and product are the same for a SaleId (based on your sample data).

var aggregated = from x in GenerateSales() 
    group x by x.SaleId into g // Assume that SaleId is the key that you want to group by
    select new Aggregate() 
    { 
        SaleId = g.Key, 
        // Additional data that are the same for all rows with the same SaleId
        Name = g.First().Name, 
        Product = g.First().Product, 
        Address = g.First().Address, 
        Phone = g.First().Phone, 
        // Calculate counts of Print Types
        NoOfPrintType1 = g.Where(x => x.PrintType == "PrintType1").Count(),
        NoOfPrintType2 = g.Where(x => x.PrintType == "PrintType2").Count(),
        NoOfPrintType3 = g.Where(x => x.PrintType == "PrintType3").Count(),
    };

The Linq statement first groups by SaleId and then creates an object for each SaleId, that comprises

• the SaleId
• the additional data like address, phone...
• the count for each known PrintType (calculated by filtering the items of the group and then counting the rows)

Below you can find a sample that generates test data and outputs the result.

Result

112 | Joe | Apple | New street 12 | 11223344 | 1 | 3 | 1
113 | Joe | Kiwi | New street 12 | 11223344 | 0 | 0 | 1
114 | Jane | Orange | New street 19 | 72754722 | 1 | 0 | 0
115 | John | Orange | New street 11 | 99236527 | 0 | 2 | 0

Sample code

using System;
using System.Collections.Generic;
using System.Linq;

namespace LinqTest
{
    class Sale 
    {
        public string SaleId { get; set; }
        public string Name { get; set; }
        public string Product { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public string PrintType { get; set; }
    }

    class Aggregate 
    {
        public string SaleId { get; set; }
        public string Name { get; set; }
        public string Product { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public int NoOfPrintType1 { get; set; }
        public int NoOfPrintType2 { get; set; }
        public int NoOfPrintType3 { get; set; }

        public override string ToString() 
        {
            return $"{SaleId} | {Name} | {Product} | {Address} | {Phone} | {NoOfPrintType1} | {NoOfPrintType2} | {NoOfPrintType3}";
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var aggregated = from x in GenerateSales() 
                group x by x.SaleId into g // Assume that SaleId is the key that you want to group by
                select new Aggregate() 
                { 
                    SaleId = g.Key, 
                    // Additional data that are the same for all rows with the same SaleId
                    Name = g.First().Name, 
                    Product = g.First().Product, 
                    Address = g.First().Address, 
                    Phone = g.First().Phone, 
                    // Calculate counts of Print Types
                    NoOfPrintType1 = g.Where(x => x.PrintType == "PrintType1").Count(),
                    NoOfPrintType2 = g.Where(x => x.PrintType == "PrintType2").Count(),
                    NoOfPrintType3 = g.Where(x => x.PrintType == "PrintType3").Count(),
                };
            foreach(var a in aggregated)
            {
                Console.WriteLine(a.ToString());
            }
        }

        static IEnumerable<Sale> GenerateSales()
        {
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType1" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType3" };
            yield return new Sale() { SaleId = "113", Name = "Joe", Product = "Kiwi", Address = "New street 12", Phone = "11223344", PrintType = "PrintType3" };
            yield return new Sale() { SaleId = "114", Name = "Jane", Product = "Orange", Address = "New street 19", Phone = "72754722", PrintType = "PrintType1" };
            yield return new Sale() { SaleId = "115", Name = "John", Product = "Orange", Address = "New street 11", Phone = "99236527", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "115", Name = "John", Product = "Orange", Address = "New street 11", Phone = "99236527", PrintType = "PrintType2" };
        }
    }
}

---

Related Topics