SQL Query: COUNT value in Column, ignoring Group By - Found solutions not supported in Zoho

SQL Query: COUNT value in Column, ignoring Group By - Found solutions not supported in Zoho

Hey everyone,

I have the following database

Order  #     | SKU          | Other info | Other info2 - always first when grouped
1                 SKU1         Name          |abc
1                 SKU1         Name          |xyz
1                  SKU2        Name         |xyz
1                  SKU2        Name         |xyz
2                  SKU 2       Name          |abc
2                  SKU 2       Name         |xyz
3                  SKU3        Name         |xyz
3                  SKU3        Name          |abc
4                  SKU1        Name         |xyz
4                  SKU1        Name          |abc
4                  SKU3        Name         |xyz
4                  SKU3        Name          |abc
...                  ...              ...

With a total of 25.000 lines. 

I need to get the following:
- Get rid of the duplicates, every line is at least double in there
- Count how many rows exist with the specific Order #
- Get all columns
- From the "Other infos 2" column I always need the first value (when grouped)

So I need a final solution that looks like that:
Order  #     | SKU          | Other info | Count   | Other info2
1                 SKU1         Name            2          |abc
1                  SKU2        Name            2          |xyz
2                  SKU 2       Name            1          |abc
3                  SKU3        Name            1          |xyz
4                  SKU1        Name            2          |xyz
4                  SKU3        Name            2          |xyz
...                  ...              ...

So far, I get everything, except the counting correct:
  1.           
  2. SELECT DISTINCT
  3. "Order",
  4. "SKU",
  5. GROUP_LAST("Other info"),
  6. GROUP_First ("Other info 2"),
  7.  COUNT("Order") AS "Count"
  8. FROM  "Orders" 
  9. GROUP BY
  10.  "Order",
  11.  "SKU" 
The problem is, that it now counts only the number of orders within the Group Order, SKU - it looks like this:
Order  #     | SKU          | Other info | Count | Other info2
1                 SKU1         Name            1        |abc
1                  SKU2        Name            1        |xyz
2                  SKU 2       Name            1        |abc
3                  SKU3        Name            1        |xyz
4                  SKU1        Name            1        |xyz
4                  SKU3        Name            1        |xyz
...                  ...              ...

What I already tried:
- I made another query table, with
  2. SELECT
  3.  "Order",
  4.  COUNT("Order") AS "Count"
  5. FROM  "Orders" 
To get the right count value, and than join it with the other table. The problem is that Zoho Reports does not allow that, because with 2x 25.000 lines it would take to long.

So I tried to have a subquery within the first query:
  2. SELECT DISTINCT
  3.  "Order",
  4.  "SKU",
  5.  GROUP_LAST("Other info"),
  6. GROUP_First ("Other info 2"),
  7.  (
  8. SELECT
  9.  "Order",
  10.  COUNT("Order") AS "Count"
  11. FROM  "Orders" AS tab2 Where tab1."Order"=tab2."Order") A "Count"
  12. FROM  "Orders" AS tab1
  13. GROUP BY
  14.  "Order",
  15.   "SKU" 
Here everything worked fine, as long as I keep the WHERE out if it. I than read that Zoho is not expecting related subqueries, so this solution is also not working.

In a third step, I tried to change the source table "Orders", by adding another row with an aggregate form column, but apparently Zoho Reports does not allow columns with aggregation formulas in it.

So I really need help here, I tried 3 solutions that are all blocked by the Zoho Report limitations. Unfortunately, I also need the group function to get only the first row of "Other info 2".

Thanks and best,
Fabian

Edited to make it a little bit clearer