CODEWITHSUNDEEP
laraveleloquent
user11352561
user11352561

Reputation: 2637

Laravel - Cardinality violation error in Aggregate Sum

I am trying a Query to get Current Day, Current Week, Current Month, Current Year and Total Sales. All together as one result. I used the Query shown below:

    $currentDay = date('d');
    $currentWeek = date('w');
    $currentMonth = date('m');
    $currentYear = date('y');

    $revenues = DB::table("vw_revenue_report")
->select(
    "vw_revenue_report.channel",
    DB::raw("SUM(CASE WHEN ('DAY(created_at) = ?',$currentDay) THEN amount ELSE 0 END) daily_revenue"),    
    DB::raw("SUM(CASE WHEN ('WEEK(created_at) = ?',$currentWeek) THEN amount ELSE 0 END) weekly_revenue"),
    DB::raw("SUM(CASE WHEN ('MONTH(created_at) = ?',$currentMonth) THEN amount ELSE 0 END) monthly_revenue"),
    DB::raw("SUM(CASE WHEN ('YEAR(created_at) = ?',$currentYear) THEN amount ELSE 0 END) yearly_revenue"),
    DB::raw("SUM(vw_revenue_report.amount) total_revenue")
)
->groupBy("vw_revenue_report.channel")
->orderByRaw('vw_revenue_report.channel ASC')
    ->get();

While trying to run it, I got the error shown below:

"SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s) (SQL: select vw_revenue_report.channel, SUM(CASE WHEN ('DAY(created_at) = ?',02) THEN amount ELSE 0 END) daily_revenue, SUM(CASE WHEN ('WEEK(created_at) = ?',2) THEN amount ELSE 0 END) weekly_revenue, SUM(CASE WHEN ('MONTH(created_at) = ?',07) THEN amount ELSE 0 END) monthly_revenue, SUM(CASE WHEN ('YEAR(created_at) = ?',19) THEN amount ELSE 0 END) yearly_revenue from vw_revenue_report group by vw_revenue_report.channel order by vw_revenue_report.channel ASC, vw_revenue_report.channel asc limit 100 offset 0) ◀"

What do I do to resolve it?

Upvotes: 0

Views: 914

Answers (1)

Muhammad Bilal
Muhammad Bilal

Reputation: 517

Hopefully it will work for you

$currentDay = date('d');
$currentWeek = date('w');
$currentMonth = date('m');
$currentYear = date('y');

$revenues = DB::table("vw_revenue_report")
->select(
    "vw_revenue_report.channel","sum(amount) as Total_in_day",
    DB::raw("(select sum(amount) as tot from vw_revenue_report where WEEK(created_at) = $currentWeek) as Total_in_week"),
    DB::raw("(select sum(amount) as tot from vw_revenue_report where MONTH(created_at) = $currentMonth) as Total_in_month"),
    DB::raw("(select sum(amount) as tot from vw_revenue_report where YEAR(created_at) = $currentYear) as Total_in_year")
)
->where("DAY(created_at)",$currentDay)
->get();

Upvotes: 0

Related Questions