SQL Count of two values in one column

sql count occurrences of value in multiple columns
sql count multiple columns
sql count occurrences of value in column
sql count(distinct multiple columns)
sql select distinct values and count of each
sql count two columns from same table
sql name count column
sql multiple counts with different criteria

I have a table with customer name and Status columns. The status column has two values

Test
Live

The customers appear more than once and can be classed as either test, live or BOTH like below:

**Customer |  Status**
Logistics | Test
Logistics | Live
Ample     | Live

What I want is a query to give me a count of the number of distinct customers who fall under both statuses. So using the above table, I would count customer logistics (since it has both test and live) but not ample (since it is just live).

Any ideas?

try something like this:

select customer 
from
    (select customer, max(IsTest) as IsTest , max(IsLive) as IsLive 
     from 
         (select customer, 
              case when status='test' then 1 else 0 end as IsTest,
              case when status='live' then 1 else 0 end as IsLive   
          from table) a
     group by customer) b
where IsTest = 1 and IsLive = 1

How to get count for different columns on same table, SELECT distributor_id, COUNT(*) FROM GROUP BY level; You will get the count for each level and need to sum them all up to get the total. I do something like this where I just give each table a string name to identify it in column A, and a count for column. The 1st Count is the over-all total. And then the 12 others are filtered by Color. I can get my results by doing multiple Counts all in one query, but this gives me 13 rows of data. The goal here is to get everything on just one row. So, almost like each count would be its own column. Here is an example of the data model

You can use group by clause :

select Customer, count(*)
from table t
group by Customer
having min(status) <> max(status); 

If you want it with specific status then include where clause :

select Customer, count(*)
from table t
where status in ('Test', 'Live')
group by Customer
having count(distinct status) = 2;

EDIT : If you want other columns too, then i would prefer :

select t.*
from table t
where exists (select 1 from table t1 where t1.Customer = t.Customer and t1.status <> t.status);

SQL to find the number of distinct values in a column, How do I count the same values in a column in SQL? No. GROUP BY simply groups multiple rows with the same value in the grouped column in a single row. So, if you have 100 rows with the same value for name , yes, it will return a single row, but if you have 100 rows with 10 different values for the name column (and you GROUP BY name ), it will return 10 rows, each with a distinct name .

you can use group by clause to get your desire output.

select Customer, count(*) from table t group by Customer having min(status) <> max(status);

(CASE WHEN StatusID = 4 THEN ClaimID END) FROM dbo. We can count during aggregation using GROUP BY to make distinct when needed after the select statement to show the data with counts. Remember that you must include the columns that are before the count in GROUP BY: SELECT &lt;column&gt;, COUNT(&lt;column&gt;)

Two of the SQL queries demonstrated earlier are shown here with ORDER BY added. SELECT year, count(1). select COUNT('x') as Everything, COUNT(case when OutcomeID = 36 then 'x' else NULL end) as Sales, COUNT(case when OutcomeID <> 36 then 'x' else NULL end) as Other from YourTable. Alternatively, you can use SUM, like bluefeet demonstrated. Share a link to this answer. improve this answer. answered Jun 19 '13 at 14:41. 149 silver badges.

SQL COUNT() with GROUP by: The use of COUNT() function in A combination of same values (on a column) will be treated as an individual group. COUNT(*) FROM agents GROUP BY working_area ORDER BY 2 ;. I am using Microsoft SQL Server 2000. I am using JDBC in a java bean to connect to the database and I am using SQL to get data [SELECT] from a particular table. With SQL I am trying to combine column A and column B into a single column, say column Z to sort [ORDER BY] by column Z.

Use the aggregate FILTER clause in Postgres 9.4+:. SELECT count(*) FILTER (​WHERE rank = ANY ('{a,b,v}')) AS myvalues , count(*) FILTER  The SQL COUNT(), AVG() and SUM() Functions The COUNT() function returns the number of rows that matches a specified criterion. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column.

Comments
  • SELECT COUNT(*) FROM tablename WHERE condition1 AND condition2 ?
  • that's great, but how would i add this into a query which i am already selecting other columns?
  • you can join this query to your existing query to show more cols
  • Thank you for your response, what if i am selecting other columns too, can this still hold?
  • @Taz. . . If you want one or two columns then include them with group by, but i would prefer to use exists.