Summer Sale! All accounts are 50% off this week.

Ligonsker's avatar

Can I make only one of the columns in the select be between dates?

I currently have the following query:

UserData::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')
    )
    ->whereBetween('created_at', [Carbon::now()->subYear(), Carbon::now()])
    ->groupBy('country', 'city')

But I need only the not_active sum to use the whereBetween clause, Is it possible to do it in the same query ?

0 likes
3 replies
jlrdw's avatar

You would do just as well getting both and order by active.

Otherwise do one or the other separate.

1 like
kokoshneta's avatar

The short answer is no. A WHERE clause in SQL applies to the whole query it appears in. You cannot apply it to just a single column.

More generally, though, I cannot make any sense of your query/question at all. What are you actually trying to accomplish?

If end_date is null, then active should be 1; otherwise it should be 0. So far so good – if the thing hasn’t ended, it’s still active; if it has ended, it’s no longer active.

Similarly, if end_date is null, then not_active should be 0, otherwise 1. Makes sense, too, by the same token, but is completely redundant. Why do you need both active and not_active? By any sane naming convention, those two should be mutually exclusive. Whatever your UserData represents exactly, it cannot be both active and not active at the same time.

And then you want to restrict the not_active column to whether created_at is within the past year? What happens if end_date is null, but created_at is more than a year ago? Should not_active be 1 then? That would make both active and not_active 1, which makes no sense.

What rows is it that you want to consider not_active exactly?

1 like

Please or to participate in this conversation.