Laravel - Get data by Month and sum it (varchar)
![Multi tool use Multi tool use](http://sgv.ssvwv.com/sg/ssvwvcomimagb.png)
Multi tool use
I´ve seen similar questions but honestly, I didn´t see anything that could take me to the right way and that´s because I´m also looking for a better way of doing this.
At certain point in my app i´m using chartjs, where i need to show months and then the values(sales). For that i know(or i think) i have to retrieve data group by months, and i got here so far:
$records = TabelaAngariacao::select('preco', 'created_at')
->where('estado', '=', 'Vendido')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('m');
});
Well...as you can see, I´m not SUMING anything, and that´s because in my database my values are getting stored in varchar(with dot´s!) and I know that´s not the proper way of doing things, but at this point changing everything it´s not a solution for me right now, so how could i change that function in order to remove the dot´s and do a proper sum? I have tried using CAST but it´s giving me undefined function.
Any suggestions? Thanks for your time, regards.
--EDIT--
Hey everyone, i think i´m almost there, i didn´t know about the transform function, in there i can make some modifications on the preco(price) field which still has the problem of being a varchar, so i need to remove the dot´s in order for the sum to work, what i have is:
$records = TabelaAngariacao::select('preco', 'created_at')
->where('estado', '=', 'Vendido')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('m');
})->transform(function ($value) { //it can also be map()
$nStr = str_replace(".", "", $value->pluck('preco'));
return [
'preco_sum' => $nStr->sum()
];
});
but this does not work, because $nStr is string...what do i need to do? if i convert to float, then the sum will not work...if i leave this way, the sum between 175.000 and 1.000.000 will be 176.000 which is wrong...any thoughts?
laravel laravel-5
add a comment |
I´ve seen similar questions but honestly, I didn´t see anything that could take me to the right way and that´s because I´m also looking for a better way of doing this.
At certain point in my app i´m using chartjs, where i need to show months and then the values(sales). For that i know(or i think) i have to retrieve data group by months, and i got here so far:
$records = TabelaAngariacao::select('preco', 'created_at')
->where('estado', '=', 'Vendido')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('m');
});
Well...as you can see, I´m not SUMING anything, and that´s because in my database my values are getting stored in varchar(with dot´s!) and I know that´s not the proper way of doing things, but at this point changing everything it´s not a solution for me right now, so how could i change that function in order to remove the dot´s and do a proper sum? I have tried using CAST but it´s giving me undefined function.
Any suggestions? Thanks for your time, regards.
--EDIT--
Hey everyone, i think i´m almost there, i didn´t know about the transform function, in there i can make some modifications on the preco(price) field which still has the problem of being a varchar, so i need to remove the dot´s in order for the sum to work, what i have is:
$records = TabelaAngariacao::select('preco', 'created_at')
->where('estado', '=', 'Vendido')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('m');
})->transform(function ($value) { //it can also be map()
$nStr = str_replace(".", "", $value->pluck('preco'));
return [
'preco_sum' => $nStr->sum()
];
});
but this does not work, because $nStr is string...what do i need to do? if i convert to float, then the sum will not work...if i leave this way, the sum between 175.000 and 1.000.000 will be 176.000 which is wrong...any thoughts?
laravel laravel-5
why not usedforeach
on resultquery
next usedcarbon
to put format on created at???
– Alexander Villalobos
2 days ago
Can you show an example of the format ofpreco
value?
– Oluwatobi Samuel Omisakin
2 days ago
Yes, at this point i have values like: 160.000 or 1.000.000 or 175.000 .... i know there are wrong and i saw something about removing the dots and summing, but i don´t know how to implement that.
– Japa
2 days ago
does the dots represent thousands or they are decimal places?
– Oluwatobi Samuel Omisakin
2 days ago
add a comment |
I´ve seen similar questions but honestly, I didn´t see anything that could take me to the right way and that´s because I´m also looking for a better way of doing this.
At certain point in my app i´m using chartjs, where i need to show months and then the values(sales). For that i know(or i think) i have to retrieve data group by months, and i got here so far:
$records = TabelaAngariacao::select('preco', 'created_at')
->where('estado', '=', 'Vendido')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('m');
});
Well...as you can see, I´m not SUMING anything, and that´s because in my database my values are getting stored in varchar(with dot´s!) and I know that´s not the proper way of doing things, but at this point changing everything it´s not a solution for me right now, so how could i change that function in order to remove the dot´s and do a proper sum? I have tried using CAST but it´s giving me undefined function.
Any suggestions? Thanks for your time, regards.
--EDIT--
Hey everyone, i think i´m almost there, i didn´t know about the transform function, in there i can make some modifications on the preco(price) field which still has the problem of being a varchar, so i need to remove the dot´s in order for the sum to work, what i have is:
$records = TabelaAngariacao::select('preco', 'created_at')
->where('estado', '=', 'Vendido')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('m');
})->transform(function ($value) { //it can also be map()
$nStr = str_replace(".", "", $value->pluck('preco'));
return [
'preco_sum' => $nStr->sum()
];
});
but this does not work, because $nStr is string...what do i need to do? if i convert to float, then the sum will not work...if i leave this way, the sum between 175.000 and 1.000.000 will be 176.000 which is wrong...any thoughts?
laravel laravel-5
I´ve seen similar questions but honestly, I didn´t see anything that could take me to the right way and that´s because I´m also looking for a better way of doing this.
At certain point in my app i´m using chartjs, where i need to show months and then the values(sales). For that i know(or i think) i have to retrieve data group by months, and i got here so far:
$records = TabelaAngariacao::select('preco', 'created_at')
->where('estado', '=', 'Vendido')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('m');
});
Well...as you can see, I´m not SUMING anything, and that´s because in my database my values are getting stored in varchar(with dot´s!) and I know that´s not the proper way of doing things, but at this point changing everything it´s not a solution for me right now, so how could i change that function in order to remove the dot´s and do a proper sum? I have tried using CAST but it´s giving me undefined function.
Any suggestions? Thanks for your time, regards.
--EDIT--
Hey everyone, i think i´m almost there, i didn´t know about the transform function, in there i can make some modifications on the preco(price) field which still has the problem of being a varchar, so i need to remove the dot´s in order for the sum to work, what i have is:
$records = TabelaAngariacao::select('preco', 'created_at')
->where('estado', '=', 'Vendido')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('m');
})->transform(function ($value) { //it can also be map()
$nStr = str_replace(".", "", $value->pluck('preco'));
return [
'preco_sum' => $nStr->sum()
];
});
but this does not work, because $nStr is string...what do i need to do? if i convert to float, then the sum will not work...if i leave this way, the sum between 175.000 and 1.000.000 will be 176.000 which is wrong...any thoughts?
laravel laravel-5
laravel laravel-5
edited 2 days ago
asked 2 days ago
Japa
356218
356218
why not usedforeach
on resultquery
next usedcarbon
to put format on created at???
– Alexander Villalobos
2 days ago
Can you show an example of the format ofpreco
value?
– Oluwatobi Samuel Omisakin
2 days ago
Yes, at this point i have values like: 160.000 or 1.000.000 or 175.000 .... i know there are wrong and i saw something about removing the dots and summing, but i don´t know how to implement that.
– Japa
2 days ago
does the dots represent thousands or they are decimal places?
– Oluwatobi Samuel Omisakin
2 days ago
add a comment |
why not usedforeach
on resultquery
next usedcarbon
to put format on created at???
– Alexander Villalobos
2 days ago
Can you show an example of the format ofpreco
value?
– Oluwatobi Samuel Omisakin
2 days ago
Yes, at this point i have values like: 160.000 or 1.000.000 or 175.000 .... i know there are wrong and i saw something about removing the dots and summing, but i don´t know how to implement that.
– Japa
2 days ago
does the dots represent thousands or they are decimal places?
– Oluwatobi Samuel Omisakin
2 days ago
why not used
foreach
on result query
next used carbon
to put format on created at???– Alexander Villalobos
2 days ago
why not used
foreach
on result query
next used carbon
to put format on created at???– Alexander Villalobos
2 days ago
Can you show an example of the format of
preco
value?– Oluwatobi Samuel Omisakin
2 days ago
Can you show an example of the format of
preco
value?– Oluwatobi Samuel Omisakin
2 days ago
Yes, at this point i have values like: 160.000 or 1.000.000 or 175.000 .... i know there are wrong and i saw something about removing the dots and summing, but i don´t know how to implement that.
– Japa
2 days ago
Yes, at this point i have values like: 160.000 or 1.000.000 or 175.000 .... i know there are wrong and i saw something about removing the dots and summing, but i don´t know how to implement that.
– Japa
2 days ago
does the dots represent thousands or they are decimal places?
– Oluwatobi Samuel Omisakin
2 days ago
does the dots represent thousands or they are decimal places?
– Oluwatobi Samuel Omisakin
2 days ago
add a comment |
3 Answers
3
active
oldest
votes
I'm doing this to totalize and group by months
$ventas = Operacion::select([
DB::raw("DATE_FORMAT(created_at, '%Y') year"),
DB::raw("DATE_FORMAT(created_at, '%m') month"),
DB::raw("SUM(1) cantidad"),
DB::raw("SUM(operacion_total) total"),
DB::raw("SUM(CASE WHEN estado = 'OPEFIN' THEN operacion_total ELSE 0 END) total_finalizado")
])->groupBy('year')->groupBy('month')->get();
And if you need to cast some varchar you could do
CAST(operacion_total as DECIMAL(9,2))
so a column in select could be
DB::raw("SUM(CAST(operacion_total as DECIMAL(9,2))) total"),
Please try this and let me know how it works :)
Hi Manuel, your solution does not take in account that my field "preco"(prices) is a varchar...and the values inside this field have dots..like 175.000 ... i know it´s wrong but at this point i cannot change this
– Japa
2 days ago
add a comment |
try this query .
$records = TabelaAngariacao::select(
DB::raw('sum(cast(preco as double precision)) as sums'),
DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"))
->where('estado', '=', 'Vendido')
->groupby('months')->get();
Hi alexander, thak´s for answering, but i need to sum the 'preco'(prices) and this field is varchar and using dot´s....what i am trying to achieve is the total amount per month
– Japa
2 days ago
This is returning 10, 11, 12, 12 which means the months, but how do you wrap this in order to give you an array where the the preco(price) is summed? so the 12 only appears once but with the values summed?...i´m sorry but could you explain to me better?
– Japa
2 days ago
try this newquery
sum per month
– Alexander Villalobos
2 days ago
Hey Alexander, i´m getting this: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months fromtabelaAngariac' at line 1 (SQL: select sum(cast(preco as double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariacao` whereestado
= Vendido group by months)
– Japa
2 days ago
ok go to folder laravel go toconfig
-> database and searchstrict
change to false
– Alexander Villalobos
2 days ago
|
show 2 more comments
Friend, if you want to make use of collection to group and also sum the values in group I believe its not too late.
Let's use the example below:
I'll make a sample result of your eloquent query with the following... Say we have users
$users = collect();
foreach (range(1, 9) as $item) {
foreach (range(1, 9) as $ninja) {
$users->push([
'created_at' => carbon('2018-0' . $item),
'preco' => (string)'160.00' . $item
]);
}
}
The above should have something like this...
[
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
...
]
We then group by, and transform (or map) the result using...
$users->map(function ($user) {
$user['preco'] = str_replace('.', '', $user['preco']);
return $user;
})->groupBy(function ($user) {
return Carbon::parse($user['created_at'])->format('m');
})->transform(function ($value, $key) { //it can also be map()
return [
'preco_sum' => $value->pluck('preco')->sum()
];
});
Here we have the sum of each of the group of months...
UPDATE
If the record is big, then you can cut down the map
ping by using Accessor
You could have something as follows that formats your 'preco' field in your model like so:
/**
* Get the user's first name.
*
* @param string $value
* @return string
*/
public function getPrecoAttribute($value)
{
return str_replace('.', '', $value);
}
This means when Laravel is building the Eloquent objects, this value would be formatted as specified which means you can remove the first map()
from the result.
PS: you might need a mutator to change it to the format you want it in the database when saving.
Cheers!
Hi, your answer is almost taing me to the right direction...because of the "transform" function, i can work the $value that has dot´s...how do you remove the dots in order to sum?
– Japa
2 days ago
because the way you have it, it sums the wrong way because it´s considering the dot´s
– Japa
2 days ago
@Japa You can run a map on the result as shown in my updated answer or you can create an Accessor See: laravel.com/docs/5.7/eloquent-mutators#accessors-and-mutators
– Oluwatobi Samuel Omisakin
2 days ago
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53944966%2flaravel-get-data-by-month-and-sum-it-varchar%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
I'm doing this to totalize and group by months
$ventas = Operacion::select([
DB::raw("DATE_FORMAT(created_at, '%Y') year"),
DB::raw("DATE_FORMAT(created_at, '%m') month"),
DB::raw("SUM(1) cantidad"),
DB::raw("SUM(operacion_total) total"),
DB::raw("SUM(CASE WHEN estado = 'OPEFIN' THEN operacion_total ELSE 0 END) total_finalizado")
])->groupBy('year')->groupBy('month')->get();
And if you need to cast some varchar you could do
CAST(operacion_total as DECIMAL(9,2))
so a column in select could be
DB::raw("SUM(CAST(operacion_total as DECIMAL(9,2))) total"),
Please try this and let me know how it works :)
Hi Manuel, your solution does not take in account that my field "preco"(prices) is a varchar...and the values inside this field have dots..like 175.000 ... i know it´s wrong but at this point i cannot change this
– Japa
2 days ago
add a comment |
I'm doing this to totalize and group by months
$ventas = Operacion::select([
DB::raw("DATE_FORMAT(created_at, '%Y') year"),
DB::raw("DATE_FORMAT(created_at, '%m') month"),
DB::raw("SUM(1) cantidad"),
DB::raw("SUM(operacion_total) total"),
DB::raw("SUM(CASE WHEN estado = 'OPEFIN' THEN operacion_total ELSE 0 END) total_finalizado")
])->groupBy('year')->groupBy('month')->get();
And if you need to cast some varchar you could do
CAST(operacion_total as DECIMAL(9,2))
so a column in select could be
DB::raw("SUM(CAST(operacion_total as DECIMAL(9,2))) total"),
Please try this and let me know how it works :)
Hi Manuel, your solution does not take in account that my field "preco"(prices) is a varchar...and the values inside this field have dots..like 175.000 ... i know it´s wrong but at this point i cannot change this
– Japa
2 days ago
add a comment |
I'm doing this to totalize and group by months
$ventas = Operacion::select([
DB::raw("DATE_FORMAT(created_at, '%Y') year"),
DB::raw("DATE_FORMAT(created_at, '%m') month"),
DB::raw("SUM(1) cantidad"),
DB::raw("SUM(operacion_total) total"),
DB::raw("SUM(CASE WHEN estado = 'OPEFIN' THEN operacion_total ELSE 0 END) total_finalizado")
])->groupBy('year')->groupBy('month')->get();
And if you need to cast some varchar you could do
CAST(operacion_total as DECIMAL(9,2))
so a column in select could be
DB::raw("SUM(CAST(operacion_total as DECIMAL(9,2))) total"),
Please try this and let me know how it works :)
I'm doing this to totalize and group by months
$ventas = Operacion::select([
DB::raw("DATE_FORMAT(created_at, '%Y') year"),
DB::raw("DATE_FORMAT(created_at, '%m') month"),
DB::raw("SUM(1) cantidad"),
DB::raw("SUM(operacion_total) total"),
DB::raw("SUM(CASE WHEN estado = 'OPEFIN' THEN operacion_total ELSE 0 END) total_finalizado")
])->groupBy('year')->groupBy('month')->get();
And if you need to cast some varchar you could do
CAST(operacion_total as DECIMAL(9,2))
so a column in select could be
DB::raw("SUM(CAST(operacion_total as DECIMAL(9,2))) total"),
Please try this and let me know how it works :)
answered 2 days ago
![](https://lh5.googleusercontent.com/-SEakGc0eZbE/AAAAAAAAAAI/AAAAAAAAGwU/YBDyfynMsFU/photo.jpg?sz=32)
![](https://lh5.googleusercontent.com/-SEakGc0eZbE/AAAAAAAAAAI/AAAAAAAAGwU/YBDyfynMsFU/photo.jpg?sz=32)
Manuel Eduardo Romero
1515
1515
Hi Manuel, your solution does not take in account that my field "preco"(prices) is a varchar...and the values inside this field have dots..like 175.000 ... i know it´s wrong but at this point i cannot change this
– Japa
2 days ago
add a comment |
Hi Manuel, your solution does not take in account that my field "preco"(prices) is a varchar...and the values inside this field have dots..like 175.000 ... i know it´s wrong but at this point i cannot change this
– Japa
2 days ago
Hi Manuel, your solution does not take in account that my field "preco"(prices) is a varchar...and the values inside this field have dots..like 175.000 ... i know it´s wrong but at this point i cannot change this
– Japa
2 days ago
Hi Manuel, your solution does not take in account that my field "preco"(prices) is a varchar...and the values inside this field have dots..like 175.000 ... i know it´s wrong but at this point i cannot change this
– Japa
2 days ago
add a comment |
try this query .
$records = TabelaAngariacao::select(
DB::raw('sum(cast(preco as double precision)) as sums'),
DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"))
->where('estado', '=', 'Vendido')
->groupby('months')->get();
Hi alexander, thak´s for answering, but i need to sum the 'preco'(prices) and this field is varchar and using dot´s....what i am trying to achieve is the total amount per month
– Japa
2 days ago
This is returning 10, 11, 12, 12 which means the months, but how do you wrap this in order to give you an array where the the preco(price) is summed? so the 12 only appears once but with the values summed?...i´m sorry but could you explain to me better?
– Japa
2 days ago
try this newquery
sum per month
– Alexander Villalobos
2 days ago
Hey Alexander, i´m getting this: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months fromtabelaAngariac' at line 1 (SQL: select sum(cast(preco as double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariacao` whereestado
= Vendido group by months)
– Japa
2 days ago
ok go to folder laravel go toconfig
-> database and searchstrict
change to false
– Alexander Villalobos
2 days ago
|
show 2 more comments
try this query .
$records = TabelaAngariacao::select(
DB::raw('sum(cast(preco as double precision)) as sums'),
DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"))
->where('estado', '=', 'Vendido')
->groupby('months')->get();
Hi alexander, thak´s for answering, but i need to sum the 'preco'(prices) and this field is varchar and using dot´s....what i am trying to achieve is the total amount per month
– Japa
2 days ago
This is returning 10, 11, 12, 12 which means the months, but how do you wrap this in order to give you an array where the the preco(price) is summed? so the 12 only appears once but with the values summed?...i´m sorry but could you explain to me better?
– Japa
2 days ago
try this newquery
sum per month
– Alexander Villalobos
2 days ago
Hey Alexander, i´m getting this: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months fromtabelaAngariac' at line 1 (SQL: select sum(cast(preco as double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariacao` whereestado
= Vendido group by months)
– Japa
2 days ago
ok go to folder laravel go toconfig
-> database and searchstrict
change to false
– Alexander Villalobos
2 days ago
|
show 2 more comments
try this query .
$records = TabelaAngariacao::select(
DB::raw('sum(cast(preco as double precision)) as sums'),
DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"))
->where('estado', '=', 'Vendido')
->groupby('months')->get();
try this query .
$records = TabelaAngariacao::select(
DB::raw('sum(cast(preco as double precision)) as sums'),
DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"))
->where('estado', '=', 'Vendido')
->groupby('months')->get();
edited 2 days ago
answered 2 days ago
Alexander Villalobos
398210
398210
Hi alexander, thak´s for answering, but i need to sum the 'preco'(prices) and this field is varchar and using dot´s....what i am trying to achieve is the total amount per month
– Japa
2 days ago
This is returning 10, 11, 12, 12 which means the months, but how do you wrap this in order to give you an array where the the preco(price) is summed? so the 12 only appears once but with the values summed?...i´m sorry but could you explain to me better?
– Japa
2 days ago
try this newquery
sum per month
– Alexander Villalobos
2 days ago
Hey Alexander, i´m getting this: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months fromtabelaAngariac' at line 1 (SQL: select sum(cast(preco as double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariacao` whereestado
= Vendido group by months)
– Japa
2 days ago
ok go to folder laravel go toconfig
-> database and searchstrict
change to false
– Alexander Villalobos
2 days ago
|
show 2 more comments
Hi alexander, thak´s for answering, but i need to sum the 'preco'(prices) and this field is varchar and using dot´s....what i am trying to achieve is the total amount per month
– Japa
2 days ago
This is returning 10, 11, 12, 12 which means the months, but how do you wrap this in order to give you an array where the the preco(price) is summed? so the 12 only appears once but with the values summed?...i´m sorry but could you explain to me better?
– Japa
2 days ago
try this newquery
sum per month
– Alexander Villalobos
2 days ago
Hey Alexander, i´m getting this: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months fromtabelaAngariac' at line 1 (SQL: select sum(cast(preco as double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariacao` whereestado
= Vendido group by months)
– Japa
2 days ago
ok go to folder laravel go toconfig
-> database and searchstrict
change to false
– Alexander Villalobos
2 days ago
Hi alexander, thak´s for answering, but i need to sum the 'preco'(prices) and this field is varchar and using dot´s....what i am trying to achieve is the total amount per month
– Japa
2 days ago
Hi alexander, thak´s for answering, but i need to sum the 'preco'(prices) and this field is varchar and using dot´s....what i am trying to achieve is the total amount per month
– Japa
2 days ago
This is returning 10, 11, 12, 12 which means the months, but how do you wrap this in order to give you an array where the the preco(price) is summed? so the 12 only appears once but with the values summed?...i´m sorry but could you explain to me better?
– Japa
2 days ago
This is returning 10, 11, 12, 12 which means the months, but how do you wrap this in order to give you an array where the the preco(price) is summed? so the 12 only appears once but with the values summed?...i´m sorry but could you explain to me better?
– Japa
2 days ago
try this new
query
sum per month– Alexander Villalobos
2 days ago
try this new
query
sum per month– Alexander Villalobos
2 days ago
Hey Alexander, i´m getting this: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariac' at line 1 (SQL: select sum(cast(preco as double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariacao` where estado
= Vendido group by months)– Japa
2 days ago
Hey Alexander, i´m getting this: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariac' at line 1 (SQL: select sum(cast(preco as double)) as sums, DATE_FORMAT(created_at,'%M %Y') as months from
tabelaAngariacao` where estado
= Vendido group by months)– Japa
2 days ago
ok go to folder laravel go to
config
-> database and search strict
change to false– Alexander Villalobos
2 days ago
ok go to folder laravel go to
config
-> database and search strict
change to false– Alexander Villalobos
2 days ago
|
show 2 more comments
Friend, if you want to make use of collection to group and also sum the values in group I believe its not too late.
Let's use the example below:
I'll make a sample result of your eloquent query with the following... Say we have users
$users = collect();
foreach (range(1, 9) as $item) {
foreach (range(1, 9) as $ninja) {
$users->push([
'created_at' => carbon('2018-0' . $item),
'preco' => (string)'160.00' . $item
]);
}
}
The above should have something like this...
[
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
...
]
We then group by, and transform (or map) the result using...
$users->map(function ($user) {
$user['preco'] = str_replace('.', '', $user['preco']);
return $user;
})->groupBy(function ($user) {
return Carbon::parse($user['created_at'])->format('m');
})->transform(function ($value, $key) { //it can also be map()
return [
'preco_sum' => $value->pluck('preco')->sum()
];
});
Here we have the sum of each of the group of months...
UPDATE
If the record is big, then you can cut down the map
ping by using Accessor
You could have something as follows that formats your 'preco' field in your model like so:
/**
* Get the user's first name.
*
* @param string $value
* @return string
*/
public function getPrecoAttribute($value)
{
return str_replace('.', '', $value);
}
This means when Laravel is building the Eloquent objects, this value would be formatted as specified which means you can remove the first map()
from the result.
PS: you might need a mutator to change it to the format you want it in the database when saving.
Cheers!
Hi, your answer is almost taing me to the right direction...because of the "transform" function, i can work the $value that has dot´s...how do you remove the dots in order to sum?
– Japa
2 days ago
because the way you have it, it sums the wrong way because it´s considering the dot´s
– Japa
2 days ago
@Japa You can run a map on the result as shown in my updated answer or you can create an Accessor See: laravel.com/docs/5.7/eloquent-mutators#accessors-and-mutators
– Oluwatobi Samuel Omisakin
2 days ago
add a comment |
Friend, if you want to make use of collection to group and also sum the values in group I believe its not too late.
Let's use the example below:
I'll make a sample result of your eloquent query with the following... Say we have users
$users = collect();
foreach (range(1, 9) as $item) {
foreach (range(1, 9) as $ninja) {
$users->push([
'created_at' => carbon('2018-0' . $item),
'preco' => (string)'160.00' . $item
]);
}
}
The above should have something like this...
[
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
...
]
We then group by, and transform (or map) the result using...
$users->map(function ($user) {
$user['preco'] = str_replace('.', '', $user['preco']);
return $user;
})->groupBy(function ($user) {
return Carbon::parse($user['created_at'])->format('m');
})->transform(function ($value, $key) { //it can also be map()
return [
'preco_sum' => $value->pluck('preco')->sum()
];
});
Here we have the sum of each of the group of months...
UPDATE
If the record is big, then you can cut down the map
ping by using Accessor
You could have something as follows that formats your 'preco' field in your model like so:
/**
* Get the user's first name.
*
* @param string $value
* @return string
*/
public function getPrecoAttribute($value)
{
return str_replace('.', '', $value);
}
This means when Laravel is building the Eloquent objects, this value would be formatted as specified which means you can remove the first map()
from the result.
PS: you might need a mutator to change it to the format you want it in the database when saving.
Cheers!
Hi, your answer is almost taing me to the right direction...because of the "transform" function, i can work the $value that has dot´s...how do you remove the dots in order to sum?
– Japa
2 days ago
because the way you have it, it sums the wrong way because it´s considering the dot´s
– Japa
2 days ago
@Japa You can run a map on the result as shown in my updated answer or you can create an Accessor See: laravel.com/docs/5.7/eloquent-mutators#accessors-and-mutators
– Oluwatobi Samuel Omisakin
2 days ago
add a comment |
Friend, if you want to make use of collection to group and also sum the values in group I believe its not too late.
Let's use the example below:
I'll make a sample result of your eloquent query with the following... Say we have users
$users = collect();
foreach (range(1, 9) as $item) {
foreach (range(1, 9) as $ninja) {
$users->push([
'created_at' => carbon('2018-0' . $item),
'preco' => (string)'160.00' . $item
]);
}
}
The above should have something like this...
[
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
...
]
We then group by, and transform (or map) the result using...
$users->map(function ($user) {
$user['preco'] = str_replace('.', '', $user['preco']);
return $user;
})->groupBy(function ($user) {
return Carbon::parse($user['created_at'])->format('m');
})->transform(function ($value, $key) { //it can also be map()
return [
'preco_sum' => $value->pluck('preco')->sum()
];
});
Here we have the sum of each of the group of months...
UPDATE
If the record is big, then you can cut down the map
ping by using Accessor
You could have something as follows that formats your 'preco' field in your model like so:
/**
* Get the user's first name.
*
* @param string $value
* @return string
*/
public function getPrecoAttribute($value)
{
return str_replace('.', '', $value);
}
This means when Laravel is building the Eloquent objects, this value would be formatted as specified which means you can remove the first map()
from the result.
PS: you might need a mutator to change it to the format you want it in the database when saving.
Cheers!
Friend, if you want to make use of collection to group and also sum the values in group I believe its not too late.
Let's use the example below:
I'll make a sample result of your eloquent query with the following... Say we have users
$users = collect();
foreach (range(1, 9) as $item) {
foreach (range(1, 9) as $ninja) {
$users->push([
'created_at' => carbon('2018-0' . $item),
'preco' => (string)'160.00' . $item
]);
}
}
The above should have something like this...
[
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
{
"created_at": "2018-01-01 00:00:00",
"preco": "160.001"
},
...
]
We then group by, and transform (or map) the result using...
$users->map(function ($user) {
$user['preco'] = str_replace('.', '', $user['preco']);
return $user;
})->groupBy(function ($user) {
return Carbon::parse($user['created_at'])->format('m');
})->transform(function ($value, $key) { //it can also be map()
return [
'preco_sum' => $value->pluck('preco')->sum()
];
});
Here we have the sum of each of the group of months...
UPDATE
If the record is big, then you can cut down the map
ping by using Accessor
You could have something as follows that formats your 'preco' field in your model like so:
/**
* Get the user's first name.
*
* @param string $value
* @return string
*/
public function getPrecoAttribute($value)
{
return str_replace('.', '', $value);
}
This means when Laravel is building the Eloquent objects, this value would be formatted as specified which means you can remove the first map()
from the result.
PS: you might need a mutator to change it to the format you want it in the database when saving.
Cheers!
edited 2 days ago
answered 2 days ago
![](https://lh6.googleusercontent.com/-fyL9R-T0ylk/AAAAAAAAAAI/AAAAAAAAABo/EOOBqt7Z2v4/photo.jpg?sz=32)
![](https://lh6.googleusercontent.com/-fyL9R-T0ylk/AAAAAAAAAAI/AAAAAAAAABo/EOOBqt7Z2v4/photo.jpg?sz=32)
Oluwatobi Samuel Omisakin
3,10111130
3,10111130
Hi, your answer is almost taing me to the right direction...because of the "transform" function, i can work the $value that has dot´s...how do you remove the dots in order to sum?
– Japa
2 days ago
because the way you have it, it sums the wrong way because it´s considering the dot´s
– Japa
2 days ago
@Japa You can run a map on the result as shown in my updated answer or you can create an Accessor See: laravel.com/docs/5.7/eloquent-mutators#accessors-and-mutators
– Oluwatobi Samuel Omisakin
2 days ago
add a comment |
Hi, your answer is almost taing me to the right direction...because of the "transform" function, i can work the $value that has dot´s...how do you remove the dots in order to sum?
– Japa
2 days ago
because the way you have it, it sums the wrong way because it´s considering the dot´s
– Japa
2 days ago
@Japa You can run a map on the result as shown in my updated answer or you can create an Accessor See: laravel.com/docs/5.7/eloquent-mutators#accessors-and-mutators
– Oluwatobi Samuel Omisakin
2 days ago
Hi, your answer is almost taing me to the right direction...because of the "transform" function, i can work the $value that has dot´s...how do you remove the dots in order to sum?
– Japa
2 days ago
Hi, your answer is almost taing me to the right direction...because of the "transform" function, i can work the $value that has dot´s...how do you remove the dots in order to sum?
– Japa
2 days ago
because the way you have it, it sums the wrong way because it´s considering the dot´s
– Japa
2 days ago
because the way you have it, it sums the wrong way because it´s considering the dot´s
– Japa
2 days ago
@Japa You can run a map on the result as shown in my updated answer or you can create an Accessor See: laravel.com/docs/5.7/eloquent-mutators#accessors-and-mutators
– Oluwatobi Samuel Omisakin
2 days ago
@Japa You can run a map on the result as shown in my updated answer or you can create an Accessor See: laravel.com/docs/5.7/eloquent-mutators#accessors-and-mutators
– Oluwatobi Samuel Omisakin
2 days ago
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53944966%2flaravel-get-data-by-month-and-sum-it-varchar%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
qeMk7UFormj,IWyOMuK45 jf2G2hVMVo,cbVhIHbRbPIL5apttVyzM9,iypF02 LO
why not used
foreach
on resultquery
next usedcarbon
to put format on created at???– Alexander Villalobos
2 days ago
Can you show an example of the format of
preco
value?– Oluwatobi Samuel Omisakin
2 days ago
Yes, at this point i have values like: 160.000 or 1.000.000 or 175.000 .... i know there are wrong and i saw something about removing the dots and summing, but i don´t know how to implement that.
– Japa
2 days ago
does the dots represent thousands or they are decimal places?
– Oluwatobi Samuel Omisakin
2 days ago