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
How to Count All Rows with The Same Id with Count
Sql Server Queries Case Sensitivity
Insert-Output Including Column from Other Table
What Can Happen as a Result of Using (Nolock) on Every Select in SQL Server
Running a SQLite3 Script from Command Line
Sql Server 2008 Change Data Capture Vs Triggers in Audit Trail
Rails Pg::Undefinedtable: Error: Missing From-Clause Entry for Table
How to Exclude Tables from Sp_Msforeachtable
Oracle Subquery Does Not See the Variable from the Outer Block 2 Levels Up
How to Execute SQL Queries in Apache Spark
If Exists Statement in SQL to Linq
Sql Server: How to Perform Rtrim on All Varchar Columns of a Table
How to Create a SQLite3 Database File Using a SQL Command File
Efficiently Duplicate Some Rows in Postgresql Table
Why Is My Left Join Not Returning Nulls
How to Get Array/Bag of Elements from Hive Group by Operator