How to add another column of COUNT with different query?-CodePudding

I have a users table that has end_date column. If it is NULL, it means the user is active and if it has a date stamp he's not active anymore.

I want to group users by country and city, and display the number of total active users and total not active users.

I already had the query for showing the number of non-active users:

UserData::select(
DB::raw('count(*) as not_active, country, city'))
->whereNotNull('end_date')
->groupBy('country', 'city')
->paginate('150');

And the above adds a column of non-active users per group. But I couldn't find out how to add the second column to the above query (with pagination) that would display the number of still active users on each group.

The following query does get the correct column, but separately, and I didn't find out how to merge it with the pagination query:

UserData::select(
DB::raw('count(*) as active, country, city'))
->whereNull('end_date')
->groupBy('country', 'city')

The result should look something like that:

  country   |    city   | active | not active
------------|-----------|--------|------------
Sweden      | Stockholm |    5   |     1
Switzerland |   Bern    |    3   |     13

CodePudding user response：

You could use a combination of SUM with CASE WHEN.

UserData::query()
    ->select(
        country,
        city,
        DB::raw('SUM(CASE WHEN end_date IS NULL THEN 1 ELSE 0 END) AS active'),
        DB::raw('SUM(CASE WHEN end_date IS NULL THEN 0 ELSE 1 END) AS not active')
    )
    ->groupBy('country', 'city')