SQL: Count() based on column value
You can use a CASE expression with your aggregate to get a total based on the
sum(case when outcomeid = 36 then 1 else 0 end) SalesCount,
sum(case when outcomeid <> 36 then 1 else 0 end) NonSalesCount
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)
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
GROUP BY age) C ON S.age = C.age
SQL count rows where column value contains string
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
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
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
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'
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
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
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)
You can also use the
sum aggregate in a similar way:
select sum(case Position when 'Manager' then 1 else 0 end)
SQL : create column that count occurences of an other column values
count(*) as a window function:
select t.*, count(*) over (partition by name) as name_count
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
Sql Localdb Automatic Instance Startup Failure When Called from Visual Studio 2013, But Not SQL Server Management Studio
How to Get Array/Bag of Elements from Hive Group by Operator