Issue with whereRaw syntax query

Hi all.

Trying to filter some data for a query, below is currently what I'm using to filter out data that only has the transaction_type of 'Buy'

 $total_sales = DB::table('transactions')
                                ->whereRaw("transactions.transaction_type = 'Buy' ")
                                ->sum('transactions.gross_value');

However whenever I dd the variable, I get zero.

Is my syntax for the whereRaw statement incorrect? The column transaction_type contains either Buy or Sell, so I just want to select the rows that have Buy.

Thanks in advance

AungHtetPaing__

3 years ago

Best Answer

Level 22

@tchopa when I test it I got the result. Are you sure you have gross_value for 'Buy' transaction_type? You can also do with where clause

Transaction::where('transaction_type', 'Buy')->sum('gross_value');

1 like

Tchopa

3 years ago

Level 1

@AungHtetPaing__ Hi Aung, that hasn't worked. The schema is laid out like below:

Schema::create('transactions', function (Blueprint $table) {
            $table->id();
            $table->date('date');
            $table->decimal('gross_value', 25, 2);
            $table->string('transaction_type');
            $table->timestamps();

I want to sum the gross value of all the rows, if the row's transaction type = Buy.

click

3 years ago

Level 35

The query of @aunghtetpaing__ is correct. If you do not see any results your table is empty, you do not have a record with transaction type Buy or you are connecting to a different database than you think you are.

1 like

Tchopa

3 years ago

Level 1

@click

Can it have something to do with the formatting of the string? Because I've tried multiple different syntaxes and still to no avail.

There are records with transaction type Buy, the database (model) is correct and it isn't empty, so this is weird to me. Can a space after the word Buy cause these issues?

MohamedTammam

3 years ago

Level 51

Try that

Transaction::where('transaction_type', 'LIK', '%Buy%')->sum('gross_value');

If it works, then you have spaces before or after the "Buy" word.

1 like

Tchopa

3 years ago

Level 1

@MohamedTammam Saw this just after posting my update! But I'll try it next time, thanks for the query suggestion mate.

Tchopa

3 years ago

Level 1

Working now. Query was correct as previously mentioned, the table values contained a space after the word Buy and was an issue with the formatting. After re-importing the database from the csv, the query now executes. Thanks for all the help.

Please or to participate in this conversation.