Sqlite_orm: combine DISTINCT and COUNT

[ncoder-1]

Hi,

Is there a way to combine both DISTINCT and COUNT in the same select call?

For example:

NAME          Brand        Model
---------------------------------------
John          Apple        Iphone
John          Apple        Ipad
Bob           Samsung      S8
Bob           Blackberry   i9000
Claire        Samsung      S6

I'm trying to get the following output:

John, Apple, 1
Bob, Blackberry, Samsung, 2
Claire, Samsung, 1

I have the following which gives me the right "unique/distinct" output, but I don't have the count and right now resorting to incrementing a variable:

auto brand_distinct =
    storage.select(distinct(columns(&Inventory::name, &Inventory::brand)));

  for (auto& tin : brand_distinct) {
    cout << std::get<0>(tin) << "," << std::get<1>(tin) << endl;
  }

On the other hand, I'm able to do a select columns/count/group-by but it's not a distinct (unique) output and I get duplicate brands in my output:

auto brand_distinct =
    storage.select(columns(&Inventory::name, count(&Registration::brand)),
                   group_by(&Inventory::name),
                   order_by(&Inventory::name));

Cheers!

[fnc12]

how you'd write your query in pure SQL?

[ncoder-1]

No idea, sorry. I'm converting a bash script that used datamash as a back end and it looked like this:

datamash -t, -s -g $NAME countunique $BRAND unique $BRAND

Then, with a few awk and sort, it would provide the right output.

Best guess would be something like:
select type, count(type) from table group by type;

[fnc12]

hm, ok. I'm sure it is possible, but I need to sleep right now/ I'll answer to you tomorrow

[fnc12]

good news. I reproduced an SQL query required to fetch data you want and I reproduced it as an sqlite_orm function call.
SQL query:

SELECT NAME, GROUP_CONCAT(DISTINCT(Brand)) , COUNT(DISTINCT(Brand))  
FROM phone_users  
GROUP BY NAME

Example in C++:

        struct PhoneUser {
            std::string name;
            std::string brand;
            std::string model;
        };

        auto storage = make_storage("",
                                    make_table("phone_users",
                                               make_column("NAME", &PhoneUser::name),
                                               make_column("Brand", &PhoneUser::brand),
                                               make_column("Model", &PhoneUser::model)));
        storage.sync_schema();
        storage.insert(PhoneUser{"John", "Apple", "Iphone"});
        storage.insert(PhoneUser{"John", "Apple", "Ipad"});
        storage.insert(PhoneUser{"Bob", "Samsung", "S8"});
        storage.insert(PhoneUser{"Bob", "Blackberry", "i9000"});
        storage.insert(PhoneUser{"Claire", "Samsung", "S6"});

        auto rows = storage.select(columns(&PhoneUser::name, group_concat(distinct(&PhoneUser::brand)), count(distinct(&PhoneUser::brand))),
                                   group_by(&PhoneUser::name));
        cout << "rows count = " << rows.size() << endl;
        cout << "-----" << endl;
        for(auto &row : rows) {
            cout << std::get<0>(row) << '\t' << std::get<1>(row) << '\t' << std::get<2>(row) << endl;
        }

[ncoder-1]

Awesome! That worked perfectly. Thanks!