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

Question

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

Answer 1

You should be able to build this structure directly in the database using grouping sets. I'm assuming postgresql here (read more here https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-GROUPING-SETS) but this should also work for other Databases.

Ticket
  .select("ticket_statuses.name as status_name, departments.name as department_name, count(*) as count")
  .joins([assigned_user: :departament],:ticket_status)
  .group("grouping sets ((ticket_statuses.name, departments.name), (departments.name), ())")
  .where("ticket_statuses.status = ?", 1)

You will get:

Count grouped by status/department Count grouped by department Total Count

But you will get it as instances of your model (Ticket).

Total Count will be the one with nil for status/department and grouped by department will be the ones with nil for status.

You can tweak the order or extract the values.

Answer 2

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 chose to compute the values of "Total" as a separate (second) step, to both simplify the calculations and facilitate testing. If desired, however, one could modify the code above to compute the desired hash in a single pass through the elements of h (as @engineersmnky did) as follows.

h.each_with_object({}) do |((status, dept), v), g|
  g.update(dept=>{ status=>v, "Total"=>v }) do |_k,o,n|
    n["Total"] += o["Total"]
    o.merge(n)
  end
end

---

Here is a second way to perform the calculation, using Enumerable#group_by.

h.group_by { |(status, dept), _v| dept }
 .transform_values do |arr|
    arr.each_with_object({ "Total"=>0 }) do |((status, _dept), v), g|
      g.update(status=>v, "Total"=>g["Total"] + v)
    end
  end
 #=> {"BROWSER"=>{"Total"=>11, "Open"=>2, "Attended"=>8, "Closed"=>1},
 #    "MARKETING"=>{"Total"=>8, "Open"=>5, "Closed"=>1, "Served"=>2},
 #    "MONITORING"=>{"Total"=>12, "Open"=>2, "Closed"=>7, "Attended"=>3}}

Note the intermediate calculation.

h.group_by { |(status, dept), _v| dept }
  #=> { "BROWSER"=>[
  #       [["Open", "BROWSER"], 2], [["Attended", "BROWSER"], 8],
  #       [["Closed", "BROWSER"], 1]
  #     ],
  #     "MARKETING"=>[
  #       [["Open", "MARKETING"], 5], [["Closed", "MARKETING"], 1],
  #       [["Served", "MARKETING"], 2]
  #     ],
  #     "MONITORING"=>[
  #       [["Open", "MONITORING"], 2], [["Closed", "MONITORING"], 7],
  #       [["Attended", "MONITORING"], 3]
  #     ]
  #   }

Answer 3

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