Laravel - Get data by Month and sum it (varchar)

Multi tool use
Multi tool use












1














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?










share|improve this question
























  • 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










  • 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
















1














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?










share|improve this question
























  • 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










  • 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














1












1








1







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?










share|improve this question















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 2 days ago

























asked 2 days ago









Japa

356218




356218












  • 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










  • 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










  • 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
















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












3 Answers
3






active

oldest

votes


















0














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 :)






share|improve this answer





















  • 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



















0














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();





share|improve this answer























  • 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 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










  • ok go to folder laravel go to config -> database and search strict change to false
    – Alexander Villalobos
    2 days ago



















0














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 mapping 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!






share|improve this answer























  • 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











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
});


}
});














draft saved

draft discarded


















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









0














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 :)






share|improve this answer





















  • 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
















0














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 :)






share|improve this answer





















  • 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














0












0








0






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 :)






share|improve this answer












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 :)







share|improve this answer












share|improve this answer



share|improve this answer










answered 2 days ago









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


















  • 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













0














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();





share|improve this answer























  • 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 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










  • ok go to folder laravel go to config -> database and search strict change to false
    – Alexander Villalobos
    2 days ago
















0














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();





share|improve this answer























  • 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 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










  • ok go to folder laravel go to config -> database and search strict change to false
    – Alexander Villalobos
    2 days ago














0












0








0






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();





share|improve this answer














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();






share|improve this answer














share|improve this answer



share|improve this answer








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 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










  • ok go to folder laravel go to config -> database and search strict 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










  • 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










  • 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
















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











0














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 mapping 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!






share|improve this answer























  • 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
















0














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 mapping 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!






share|improve this answer























  • 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














0












0








0






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 mapping 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!






share|improve this answer














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 mapping 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!







share|improve this answer














share|improve this answer



share|improve this answer








edited 2 days ago

























answered 2 days ago









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


















  • 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


















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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
H 666F,ttfdb22uqfRZbkJsbZkMX umRoc I5loovgLbN9v9Tw 6bO,p K lt2fOP51zw9L1N7o2XVoJqYdQ 2Gc0,w5CMKtPk

Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas