I need to sum all values in table by links_id
CREATE TABLE links_day
(
id BIGINT DEFAULT nextval('links_day_id_seq'::regclass) PRIMARY KEY NOT NULL,
links_id BIGINT NOT NULL,
users_id BIGINT NOT NULL,
redirect_js_meta BOOLEAN,
slow_redirect_checker BOOLEAN,
cost BIGINT,
name VARCHAR(255) NOT NULL,
track_link VARCHAR(255) NOT NULL,
tcl BIGINT,
ucl BIGINT,
fcl BIGINT,
bcl BIGINT,
act BIGINT,
eng BIGINT,
usl BIGINT,
rsl BIGINT,
date DATE NOT NULL,
created_at TIMESTAMP(0),
updated_at TIMESTAMP(0)
);
I am using Laravel query builder for this:
$links = DB::table('links_day')
->select(
'id',
DB::raw('sum(tcl) as tcl'),
DB::raw('sum(ucl) as ucl'),
DB::raw('sum(fcl) as fcl'),
DB::raw('sum(bcl) as bcl'),
DB::raw('sum(act) as act'),
DB::raw('sum(eng) as eng'),
DB::raw('sum(usl) as usl'),
DB::raw('sum(rsl) as rsl'),
'links_id',
'users_id',
'redirect_js_meta',
'slow_redirect_checker',
'cost',
'name',
'track_link',
'created_at'
)
->where('users_id', Auth::user()->id)->groupBy('links_id')
I have the error for this: "id must appear in the GROUP BY clause or be used in an aggregate function". Error disappear when adding all non-sum column, but then I don't back right results, I need sum value by links_id. Any helps?
via Laky