Different Value Counts on Same Column

Different value counts on same column

You can either use CASE or DECODE statement inside the COUNT function.

  SELECT item_category,
         COUNT (*) total,
         COUNT (DECODE (item_status, 'serviceable', 1)) AS serviceable,
         COUNT (DECODE (item_status, 'under_repair', 1)) AS under_repair,
         COUNT (DECODE (item_status, 'condemned', 1)) AS condemned
    FROM mytable
GROUP BY item_category;

Output:

ITEM_CATEGORY   TOTAL   SERVICEABLE UNDER_REPAIR    CONDEMNED
----------------------------------------------------------------
chair           5       1           2               2
table           5       3           1               1

Count the frequency that a value occurs in a dataframe column

Use value_counts() as @DSM commented.

In [37]:
df = pd.DataFrame({'a':list('abssbab')})
df['a'].value_counts()

Out[37]:

b    3
a    2
s    2
dtype: int64

Also groupby and count. Many ways to skin a cat here.

In [38]:
df.groupby('a').count()

Out[38]:

   a
a   
a  2
b  3
s  2

[3 rows x 1 columns]

See the online docs.

If you wanted to add frequency back to the original dataframe use transform to return an aligned index:

In [41]:
df['freq'] = df.groupby('a')['a'].transform('count')
df

Out[41]:

   a freq
0  a    2
1  b    3
2  s    2
3  s    2
4  b    3
5  a    2
6  b    3

[7 rows x 2 columns]

SQL query - multiple COUNT on same column with different values from nested SELECT query

SELECT StudentID, 
SUM(case when Unapproved =1 then 1 else 0 end) as Late, 
SUM(case when Unapproved =2 then 1 else 0 end) as Absent 
from results where 
StudentID in (SELECT studentid FROM [Results] where StudentYearLevel='10' and Date > 20130101) 
group by StudentID

Multiple counts for certain values in same column with group by

Your query is missing a from clause but I assume this is a typo.

Consider:

select 
    accountId, 
    sum(eventType = 'start') as starts, 
    sum(eventType = 'stop') as stops 
from ???
GROUP BY accountId

Rationale: count() takes in account all values that are not null. On the other hand, as long as |eventType is not null, the condition inside the count() returns a boolean or a 0/1 valu - depending on your database. What you want is to sum() these 0/1 values.

Note that the above syntax is supported in MySQL only. If you are running Postgres (which is another database in which your original code would run), no need for this, you can use a filer clause instead:

select 
    accountId, 
    count(*) filter(where eventType = 'start') as starts, 
    count(*) filter(where eventType = 'stop') as stops 
from ???
GROUP BY accountId

How to get value counts for multiple columns at once in Pandas DataFrame?

Just call apply and pass pd.Series.value_counts:

In [212]:
df = pd.DataFrame(np.random.randint(0, 2, (10, 4)), columns=list('abcd'))
df.apply(pd.Series.value_counts)
Out[212]:
   a  b  c  d
0  4  6  4  3
1  6  4  6  7

Single column with value counts from multiple column dataframe

You could stack and take the value_counts on the resulting series:

df.stack().value_counts().sort_index()

5      1
27     4
55     1
56     1
78     1
89     1
312    2
534    1
dtype: int64

How to count values with different where clauses on the same column?

I think it might be easiest to SUM up a 1 for each row where your criteria matches:

SELECT 
    video, 
    SUM(CASE type WHEN 1 THEN 1 ELSE 0 END) as upvotes,
    SUM(CASE type WHEN 0 THEN 1 ELSE 0 END) as downvotes 
FROM 
    votes
GROUP BY 
    video;

Note, you should omit type from the GROUP BY in order to get a single row back for each video.

---

Related Topics