How can I get the sum of this field in my sql query?-CodePudding

I have this sql query

Ticket.joins([assigned_user::departament],:ticket_status).group("ticket_statuses.name","departaments.name").where("ticket_statuses.status = ?", 1).count

what does this give me back

[ "Open", "BROWSER" ] => 2,
          [ "Open", "MARKETING" ] => 5,
          [ "Open", "MONITORING" ] => 2,
             [ "Open", "SALES" ] => 7,
     [ "Open", "ADMINISTRATION" ] => 8,
          [ "Open", "COLLECTIONS" ] => 1,
           [ "Open", "EMPLOYEE" ] => 8,
           [ "Open", "SYSTEMS" ] => 6,
            [ "Open", "QUALITY" ] => 4,
    [ "Open", "TECH SUPPORT" ] => 5,
            [ "Open", "STORE" ] => 2,
           [ "Closed", "SYSTEMS" ] => 11,
          [ "Closed", "MONITORING" ] => 7,
     [ "Closed", "ADMINISTRATION" ] => 4,
            [ "Closed", "QUALITY" ] => 6,
            [ "Closed", "STORE" ] => 6,
           [ "Closed", "EMPLOYEE" ] => 2,
             [ "Closed", "SALES" ] => 3,
    [ "Closed", "TECHNICAL SUPPORT" ] => 4,
          [ "Closed", "BROWSER" ] => 1,
          [ "Closed", "COLLECTIONS" ] => 2,
          [ "Closed", "MARKETING" ] => 1,
           [ "Attended", "QUALITY" ] => 3,
          [ "Served", "EMPLOYEE" ] => 3,
           [ "Served", "WAREHOUSE" ] => 5,
         [ "Attended", "COLLECTIONS" ] => 3,
          [ "Served", "SYSTEMS" ] => 8,
            [ "Served", "SALES" ] => 5,
   [ "Attended", "TECHNICAL SUPPORT" ] => 3,
         [ "Attended", "BROWSER" ] => 8,
    [ "Attended", "ADMINISTRATION" ] => 7,
         [ "Attended", "MONITORING" ] => 3,
         [ "Served", "MARKETING" ] => 2,
        [ "In process", "EMPLOYEE" ] => 4,
       [ "In process", "COLLECTIONS" ] => 2,
         [ "In process", "QUALITY" ] => 7,
       [ "In process", "MONITORING" ] => 4,
       [ "In process", "BROWSER" ] => 4,
       [ "In progress", "MARKETING" ] => 4,
        [ "In process", "SYSTEMS" ] => 3,
  [ "In process", "ADMINISTRATION" ] => 4,
 [ "In process", "TECHNICAL SUPPORT" ] => 3,
         [ "In process", "WAREHOUSE" ] => 3,
          [ "In process", "SALES" ] => 1

I am using this query with the 'chartkick' gem that groups them in a graph by area, where when passing the mouse in each area it shows me this

Marketing
Open:5
Close:1
Attended:2
In process:4

Monitoring
Open:2
Close:7
Attended:3
In process:4

I would like to add to that information the total amount and what is displayed like this

Marketing
Open:5
Close:1
Attended:2
In process:4
Total:12

Monitoring
Open:2
Close:7
Attended:3
In process:4
Total:16

I was looking for the solution but I don't know how to achieve this

CodePudding user response：

Given your current output as h

h = {[ "Open", "BROWSER" ] => 2,[ "Open", "MARKETING" ] => 5,[ "Open", "MONITORING" ] => 2,[ "Open", "SALES" ] => 7,[ "Open", "ADMINISTRATION" ] => 8,[ "Open", "COLLECTIONS" ] => 1,[ "Open", "EMPLOYEE" ] => 8,[ "Open", "SYSTEMS" ] => 6,[ "Open", "QUALITY" ] => 4,[ "Open", "TECH SUPPORT" ] => 5,[ "Open", "STORE" ] => 2,[ "Closed", "SYSTEMS" ] => 11, [ "Closed", "MONITORING" ] => 7,[ "Closed", "ADMINISTRATION" ] => 4,[ "Closed", "QUALITY" ] => 6,[ "Closed", "STORE" ] => 6,[ "Closed", "EMPLOYEE" ] => 2,[ "Closed", "SALES" ] => 3,[ "Closed", "TECHNICAL SUPPORT" ] => 4,[ "Closed", "BROWSER" ] => 1,[ "Closed", "COLLECTIONS" ] => 2,[ "Closed", "MARKETING" ] => 1,[ "Attended", "QUALITY" ] => 3,[ "Served", "EMPLOYEE" ] => 3,[ "Served", "WAREHOUSE" ] => 5,[ "Attended", "COLLECTIONS" ] => 3,[ "Served", "SYSTEMS" ] => 8,[ "Served", "SALES" ] => 5,[ "Attended", "TECHNICAL SUPPORT" ] => 3,[ "Attended", "BROWSER" ] => 8,[ "Attended", "ADMINISTRATION" ] => 7,[ "Attended", "MONITORING" ] => 3,[ "Served", "MARKETING" ] => 2,[ "In process", "EMPLOYEE" ] => 4,[ "In process", "COLLECTIONS" ] => 2,[ "In process", "QUALITY" ] => 7,[ "In process", "MONITORING" ] => 4,[ "In process", "BROWSER" ] => 4,[ "In progress", "MARKETING" ] => 4,[ "In process", "SYSTEMS" ] => 3,[ "In process", "ADMINISTRATION" ] => 4,[ "In process", "TECHNICAL SUPPORT" ] => 3,[ "In process", "WAREHOUSE" ] => 3,[ "In process", "SALES" ] => 1}

The following will produce a Hash with the desired data structure:

h.each_with_object(Hash.new {|h,k| h[k] = {"Total"=> 0}}) do |((status,department),v),obj|
  obj[department][status] = v
  obj[department]["Total"]  = v
end 
# {"BROWSER"=>{"Total"=>15, "Open"=>2, "Closed"=>1, "Attended"=>8, "In process"=>4}, 
# "MARKETING"=>{"Total"=>12, "Open"=>5, "Closed"=>1, "Served"=>2, "In progress"=>4}, 
# "MONITORING"=>{"Total"=>16, "Open"=>2, "Closed"=>7, "Attended"=>3, "In process"=>4}, 
# "SALES"=>{"Total"=>16, "Open"=>7, "Closed"=>3, "Served"=>5, "In process"=>1}, 
# "ADMINISTRATION"=>{"Total"=>23, "Open"=>8, "Closed"=>4, "Attended"=>7, "In process"=>4}, 
# "COLLECTIONS"=>{"Total"=>8, "Open"=>1, "Closed"=>2, "Attended"=>3, "In process"=>2}, 
# "EMPLOYEE"=>{"Total"=>17, "Open"=>8, "Closed"=>2, "Served"=>3, "In process"=>4}, 
# "SYSTEMS"=>{"Total"=>28, "Open"=>6, "Closed"=>11, "Served"=>8, "In process"=>3}, 
# "QUALITY"=>{"Total"=>20, "Open"=>4, "Closed"=>6, "Attended"=>3, "In process"=>7}, 
# "TECH SUPPORT"=>{"Total"=>5, "Open"=>5}, "STORE"=>{"Total"=>8, "Open"=>2, "Closed"=>6}, 
# "TECHNICAL SUPPORT"=>{"Total"=>10, "Closed"=>4, "Attended"=>3, "In process"=>3}, 
# "WAREHOUSE"=>{"Total"=>8, "Served"=>5, "In process"=>3}}

CodePudding user response：

Suppose the given hash were as follows.

h = {
  ["Open", "BROWSER"]       =>2, ["Open", "MARKETING"]  =>5,
  ["Open", "MONITORING"]    =>2, ["Closed", "MARKETING"]=>1,
  ["Closed", "MONITORING"]  =>7, ["Attended", "BROWSER"]=>8,
  ["Attended", "MONITORING"]=>3, ["Closed", "BROWSER"]  =>1,
  ["Served", "MARKETING"]   =>2 
}

We may compute the desired hash as follows.

h.each_with_object({}) do |((status, dept), v), g|
  g.update(dept=>{ status=>v }) { |_k,o,n| o.merge(n) }
end.transform_values { |f| f.update("Total"=>f.values.sum) }
  #=> { "BROWSER"=>{"Open"=>2, "Attended"=>8, "Closed"=>1, "Total"=>11},
  #     "MARKETING"=>{"Open"=>5, "Closed"=>1, "Served"=>2, "Total"=>8},
  #     "MONITORING"=>{"Open"=>2, "Closed"=>7, "Attended"=>3, "Total"=>12}}

See:

array decomposition to better understand the way each_with_object's block variables are written;
the form of Hash#update (a.k.a. Hash#merge!) that takes a block (here { |_k,o,n| o.merge(n) }) that returns the values of keys that are present in both hashes being merged. _k holds the common key, which here is not used in the block calculation (as I've indicated by the underscore). o and n respectively hold the values of the "old" and "new" values of _k. o is the value in the hash being constructed, n is the value in the hash being merged; and
Hash#transform_values.

Note the intermediate hash that is computed:

h.each_with_object({}) do |((status, dept), v), g|
  g.update(dept=>{ status=>v }) { |_k,o,n| o.merge(n) }
end
  #=> { "BROWSER"=>{"Open"=>2, "Attended"=>8, "Closed"=>1},
  #     "MARKETING"=>{"Open"=>5, "Closed"=>1, "Served"=>2},
  #     "MONITORING"=>{"Open"=>2, "Closed"=>7, "Attended"=>3}}

I could have computed the values of "Total" at the same time as the above was obtained (much the way @engineersmnky did), but decided to do that as a separate second step, to both simplify the calculations and facilitate testing.