Select Multiple Columns Count One Column and Group by One Column in One Table

How to select multiple columns, sum one column and group by multiple columns

are you looking for this? :

select FORMAT(d.date_start, 'yyyy-MM') date_start
     , count(distinct user_id) total
from planner d
group by FORMAT(date_start, 'yyyy-MM')

Laravel groupBy - how to select multiple columns with grouped by one column

first you must disable strict mode for your mysql in config\database.php

'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', 

---

        'strict' => true,   *// **make if false***

then this query should work.

$data = DB::table('orders')
    ->where('store_id',$id)
    ->select('user_id','username','address','userphoneno', DB::raw('count(*) as total'), DB::raw('SUM(total_amount) as price'))
    ->groupBy('userphoneno')
    ->get();

count of individual column with group by on multiple columns

You can simply get the count of unique customer ids using COUNT(DISTINCT..) for every account_num and filter out those cases where count is more than 1, inside the HAVING clause:

SELECT
  ACCOUNT_NUM, 
  COUNT(DISTINCT CUSTOMER_ID) AS unique_customer_count 
FROM LINKAGE_FILE 
GROUP BY ACCOUNT_NUM
HAVING unique_customer_count > 1

Count multiple columns with group by in one query

It's hard to know how to help you without understanding the context / structure of your data, but I believe this might help you:

SELECT 
     SUM(CASE WHEN column1 IS NOT NULL THEN 1 ELSE 0 END) AS column1_count
    ,SUM(CASE WHEN column2 IS NOT NULL THEN 1 ELSE 0 END) AS column2_count
    ,SUM(CASE WHEN column3 IS NOT NULL THEN 1 ELSE 0 END) AS column3_count
FROM table

Can I count multiple columns with Group By?

You need a UNION ALL for all the columns and then count them:

select
  t.s, count(*) counter
from (
  select s from tablename union all
  select s2 from tablename union all
  select s3 from tablename
) t
where t.s is not null
group by t.s

See the demo.

Results:

| s   | counter |
| --- | ------- |
| 1   | 3       |
| 2   | 3       |
| 3   | 3       |
| 4   | 4       |

If in the columns s2 and s3 there are values that do not exist in the column s and you want them excluded, then instead of:

where t.s is not null

use

where t.s in (select s from tablename)

---

Related Topics