MySQL Count() Multiple Columns

SQL Count multiple columns

you can use group by and get maximum connections and then further do one more group by on the client to get the server that serves the most

SQL Fiddle

SELECT clientId, serverId,
max(connectionCount) as ConnectionCount, LastDate
from
(
select clientId, ServerID, 
count(*) as ConnectionCount,
max(LastDate) as LastDate
from Table1
group by clientId, ServerID
  ) t
group by clientId

How to count the occurrences in multiple column in SQL

Use union all and aggregation:

select genre, count(*)
from ((select genre_1 as genre from tv_show) union all
      (select genre_2 as genre from tv_show)
     ) g
group by genre;

With a simple modification, you can add counts for each column:

select genre, count(*), sum(first), sum(second)
from ((select genre_1 as genre, 1 as first, 0 as second from tv_show) union all
      (select genre_2 as genre, 0, 1 from tv_show)
     ) g
group by genre;

SELECT count of multiple columns with a WHERE clause

Use conditional aggregation with a CASE expression, something along these lines:

SELECT
    some_id,
    COUNT(CASE WHEN rank = 1 AND level = 30 THEN 1 END) AS bronze,
    COUNT(CASE WHEN rank = 2 AND level = 45 THEN 1 END) AS silver
FROM stats
GROUP BY some_id;

If you really want to take a tally over the entire table, then you don't need to use GROUP BY.

Is it possible to count two columns in the same query

In MySql, You can use the SUM() function over a condition, since a false condition will equal to 0, and a true one will equal to 1:

SELECT SUM(userID_follower = $myID) AS followerCount,
   SUM(userID_following = $myID) AS followingCount
FROM t1
WHERE userID_follower = $myID
   OR userID_following = $myID

How do I count multiple columns with a vertical value?

You can do this way. One query in db

$records = DB::table('your_table')->get();
$check1Count = $records->where('check_1', 1)->count();
$check2Count = $records->where('check_2', 1)->count();
$check3Count = $records->where('check_3', 1)->count();
......

Or

$records = DB::table('your_table')->get();
$columns = ['check_1', 'check_2', 'check_3', ...];
$data = [];
foreach($columns as $column) {
    $data[] = [
        'column_name' => $column,
        'count_true' => $records->where($column, 1)->count();
    ];
}

Also you can do this way but it is many query

$check1Count = DB::table('your_table')
    ->selectRaw('COUNT(check_1) as count')
    ->where('check_1', 1)
    ->first()
    ->count;

$check2Count = DB::table('your_table')
    ->selectRaw('COUNT(check_2) as count')
    ->where('check_2', 1)
    ->first()
    ->count;
.....

SQL Group By and Count on two columns

You can use a correlated sub-query:

SELECT country, city, count(city) as city_count,
       (SELECT count(*)
        FROM jobs AS j2
        WHERE j1.country = j2.country) AS country_count
FROM jobs AS j1
GROUP BY country, city

Demo here

Use sql count multiple columns yes and no in Mysql

This is a other way to do that:

SELECT 
  IF(y.yn=1,'yes','no')
, SUM(y.yn = col1) AS col1
, SUM(y.yn = col2) AS col2
, SUM(y.yn = col3) AS col3
, SUM(y.yn = col4) AS col4
FROM `tb_count` AS t
JOIN (SELECT 1 AS yn UNION ALL SELECT 2)  AS y
GROUP BY y.yn;

Select Where Count() of multiple columns is greater than one

You can group by the columns you want to deduplicate, and select the minimum date from each group:

SELECT ClaimID, ClaimLine, MIN(Date)
FROM Table
GROUP BY ClaimID, ClaimLine
ORDER BY ClaimID, ClaimLine

If you further only want to see the ClaimIDs and ClaimLines that have been duplicated, you can add a HAVING clause:

SELECT ClaimID, ClaimLine, MIN(Date)
FROM Table
GROUP BY ClaimID, ClaimLine
HAVING COUNT(*) > 1
ORDER BY ClaimID, ClaimLine

MySQL COUNT() across multiple columns

SELECT
  COUNT(DISTINCT val_1) AS val_1_count,
  COUNT(DISTINCT val_2) AS val_2_count,
  ...
FROM ...

will give you the counts for each field.

SELECT val_1, count(*) as val_1_count
FROM ...
GROUP BY val_1

will give you the counts for a value. You can use UNION to repeat this for val_1 to val_n in a single (kludgy) query.

If you want the counts over all fields, you need

SELECT val,count(*) as valcount
FROM (
  SELECT val_1 AS val FROM ...
  UNION ALL
  SELECT val_2 AS val FROM ...
  ...
) AS baseview
GROUP BY val

---

Related Topics