CODEWITHSUNDEEP
mysqllaravel
Matt
Matt

Reputation: 95

Count records based on there status laravel

I am trying to count records in a table based on there Status

+--------|-----------|-------------+
|  id    |  Record   |   Status    |
|--------|-----------|-------------|
|   1    | 20        |    A        |
|--------|-----------|-------------|
|   2    | 10        |    A        |
|--------|-----------|-------------|
|   3    | 15        |    D        |
+----------------------------------+

The problem is im using a SUM query as well as a Count query.

->select(DB::raw('SUM(Record) as commission, COUNT(Status) as status'))
->get()

Now this works, but not based on Statust Results. I have tried the following at it does not work.

->select(DB::raw('SUM(Record) as commission, COUNT(Status = A) as status'))
->get()

AND

->select(DB::raw('SUM(Record) as commission, COUNT(Status) as status WHERE Status = "A" '))
->get()

AND

->select(DB::raw('SUM(Record) as commission, COUNT(Status = "A ") as status'))
->get()

Upvotes: 1

Views: 805

Answers (1)

sagi
sagi

Reputation: 40481

Change -

COUNT(STATUS = 'A')

To:

SUM(Status = 'A')

Boolean expression returns 0/1 based on the condition. COUNT(0) will still count 1, only COUNT(NULL) doesn't add 1 to the counter. SUM() in the other hand, will sum 1 when the condition is true, and 0 when it false.

Upvotes: 2

Related Questions