Sql: Count() Based on Column Value

SQL: Count() based on column value

You can use a CASE expression with your aggregate to get a total based on the outcomeId value:

select companyId,
  sum(case when outcomeid = 36 then 1 else 0 end) SalesCount,
  sum(case when outcomeid <> 36 then 1 else 0 end) NonSalesCount
from yourtable
group by companyId;

See SQL Fiddle with Demo

How to count occurrences of a column value efficiently in SQL?

This should work:

SELECT age, count(age) 
  FROM Students 
 GROUP by age

If you need the id as well you could include the above as a sub query like so:

SELECT S.id, S.age, C.cnt
  FROM Students  S
       INNER JOIN (SELECT age, count(age) as cnt
                     FROM Students 
                    GROUP BY age) C ON S.age = C.age

SQL count rows where column value contains string

Use a case expression:

select sum(case when column_name like '%substring%' then 1 else 0 end)
from table_name ;

Or, if that is the only value you want, use filtering and count(*):

select count(*)
from table_name
where column_name like '%substring%';

The first is more suitable when you have additional columns in the query.

I should note that the SQL standard has another method of accomplishing this, using filter:

select count(*) filter (where column_name like '%substring%')
from table_name ;

However, most databases do not support this syntax.

Count Records from One Column WHERE values are different

Try this-

SELECT COUNT(*) 'Total'
, SUM(CASE WHEN column1 = 4 THEN 1 ELSE 0 END) AS 'Records w/ 4'
, SUM(CASE WHEN column1 < 4 THEN 1 ELSE 0 END) AS 'Records < 4'
FROM mytable

SQL Query to count rows that have a common column value based on another column value

First, you need a left join -- because you want all employees even those with no companies in common. Second, group by to get the count:

select jh.employee, count(jh_john.company) as num_in_common
from job_history jh left join
     job_history jh_john
     on jh_john.company = jh.company and
        jh_john.employee = 'John'
where jh.employee <> 'John'
group by jh.employee;

Note: If there could be duplicates in the table, then use count(distinct) rather than count().

Is it possible to specify condition in Count()?

If you can't just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values:

select count(case Position when 'Manager' then 1 else null end)
from ...

You can also use the sum aggregate in a similar way:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

SQL : create column that count occurences of an other column values

You want count(*) as a window function:

select t.*, count(*) over (partition by name) as name_count
from t;

---

Related Topics