CODEWITHSUNDEEP
mysqllaravel
EnzoTrompeneers
EnzoTrompeneers

Reputation: 1091

SQL Query returns my numbers into strings instead of integers

When I sort my table by column I saw that my sort was wrong for my numbers because my numbers were strings. I found a solution to do CAST(COALESCE(p.total,0) AS UNSIGNED) and now it returns them as an integer. I think it's very inconvenient that I need to do this to all my numbers? Can somebody explain me why it does behave like that or what I do wrong?

$result = DB::select(DB::raw(
    "SELECT i.date,
    u.type,
    CONCAT_WS(' ', u.first_name, u.last_name) AS consultant,
    c.name AS customer,
    CAST(COALESCE(e.pp,0) AS UNSIGNED) AS pp,
    CAST(COALESCE(e.sp,0) AS UNSIGNED) AS sp,
    CAST(COALESCE(e.sp,0) - COALESCE(e.pp,0) AS UNSIGNED) AS margin,
    CAST(COALESCE(p.total,0) AS UNSIGNED) AS total_purchase,
    CAST(COALESCE(i.total, 0) AS UNSIGNED) AS total_sales,
    CAST(COALESCE(i.total,0) - COALESCE(p.total,0) AS UNSIGNED) AS gross_margin
    FROM clockwork.invoices AS i
    INNER JOIN clockwork.timesheets AS t ON i.timesheet_id = t.id
    LEFT OUTER JOIN clockwork.purchases AS p ON t.id = p.timesheet_id
    INNER JOIN clockwork.users AS u ON i.user_id = u.id
    INNER JOIN clockwork.customers AS c ON i.customer_id = c.id
    LEFT OUTER JOIN clockwork.contract_extensions AS e ON i.extension_id = e.id
    WHERE i.date between '$request->start' and '$request->end'
    GROUP BY i.date, u.type, u.first_name, u.last_name, c.name, e.pp, e.sp, p.total, i.total
    ORDER BY u.first_name
"));

Upvotes: 0

Views: 1398

Answers (1)

MounirOnGithub
MounirOnGithub

Reputation: 699

I've found this solution right here

Maybe it resolves your question ? Regards

Upvotes: 1

Related Questions