mardi 22 décembre 2015

To show the data extracted from database horizontally using Ruby program

I have to show the data in the below specified manner using Ruby program.. Can someone help me as to how can I do it. DO have to make changes in SQlcode or Ruby code. The code is below here:

require 'sqlite3'
#db=SQLite3::Database.open ('adaptive.db')
class Database
    @@db=SQLite3::Database.open 'adaptive.db'
    def registered_users
        @@db.execute(
        "Select strftime('%Y-%m',created_at) AS Month, count(uuid) as    RegistredPeopleNo
        from users
        group by strftime('%Y-%m',created_at)
        order by Month;") do |row|
          p row
        end
      end
      def registered_devices
         @@db.execute(
         "Select strftime('%Y-%m',created_at) AS RegisteredMonth,
          count(serial_number) AS RegisteredDevicesNo
          from devices
          group by strftime('%Y-%m',created_at)
          order by RegisteredMonth;") do |row|
              p row
          end
       end
       def unregistered_users
         @@db.execute(
         "Select strftime('%Y-%m',updated_at) AS Unregister, count(id) AS Id
          from users
          where id not in(Select user_id from devices)
          group by strftime('%Y-%m',updated_at)
          order by Unregister;") do |row|
          p row
          end
       end
       def unregistered_devices
         @@db.execute(
         "Select strftime('%Y-%m',updated_at) AS Unregister,count(id) AS Id
          from devices
          where channel_id is null
          group by strftime('%Y-%m',updated_at);") do |row|
          p row
       end
     end
     def device_per_user
       @@db.execute(
       "select register AS NoOFMachines,count(id) as NoOFPeople
        from(
        Select users.id AS id,count(devices.id) AS register
        from users,devices
        where users.id=devices.user_id
        group by devices.user_id)
        group by register;") do |row|
          p row
        end
     end
  end

The run commands:

>irb
>require './database'
>db=Database.new
>db.registered_users#
The output is following:
["2015-08",23]
["2015-09",102]
["2015-10",59]
["2015-11",25]

But, I want it to displayed like this:

[["2015-08",23],["2015-09",102],["2015-10",59],["2015-11",25]]

Can some one help me with this problem... Thanks in advance

Aucun commentaire:

Enregistrer un commentaire