-2

I am using SQLite. I create a table called MyTable, with 3 columns MyID, MyOrder, MyValue, all are INTEGER types, as follows:

MyID       |     MyOrder        | MyValue
1          |         3          |       2
1          |         2          |       5
2          |         1          |       8
3          |         5          |       6

I need to select all MyID from the table, for each MyID, also return the number of rows with that MyID. In the above sample, the query should return:

        1|2
        2|1
        3|1

How to write this query?

alancc
  • 487
  • 2
  • 24
  • 68
  • 3
    The expected result doesn't match the description you gave – Mureinik Sep 24 '18 at 04:04
  • 3
    What have you tried? Both queries are going to involve a `GROUP BY` and an aggregating function like `MAX` or `COUNT` – Phil Sep 24 '18 at 04:07
  • Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) and [Is it possible to count all rows with the same id with COUNT?](https://stackoverflow.com/questions/30932283/is-it-possible-to-count-all-rows-with-the-same-id-with-count) – Phil Sep 24 '18 at 04:16

1 Answers1

0
SELECT MyID, COUNT(MyID) 
FROM Table
GROUP BY MyID

Reference 1

Reference 2

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71