On Thu May 26, 2016 at 02:29:50PM +0800, Jim Wang wrote:
> hi all
> a table as follow:
> id score
> 2 10
> 3 20
> 5 10
> 3 20
> 2 30
> 2 30
Converting that into SQL we have:
CREATE TABLE a(
id INTEGER,
score INTEGER
);
INSERT INTO a VALUES(2,10);
INSERT INTO a VALUES(3,20);
INSERT INTO a VALUES(5,10);
INSERT INTO a VALUES(3,20);
INSERT INTO a VALUES(2,30);
INSERT INTO a VALUES(2,30);
> how could I select the table as follow and the count can tell me:
> the id 2 hava 3,the id 3 have 2 the id 5 have 1.
To calculate the number of occurances of each id you can use the
following:
SELECT
id,
COUNT(*) AS id_count
FROM
a
GROUP BY
id
ORDER BY
id
;
id id_count
---------- ----------
2 3
3 2
5 1
> count id score
> 3 2 10
> 2 3 20
> 1 5 10
> 2 3 20
> 3 2 30
> 3 2 30
I do not see any possibility of ordering rows the same way you have.
However to obtain the equivalent set you could use a sub query or a
common table expression (CTE):
CTE:
WITH x
AS (
SELECT
a.id,
COUNT(*) AS id_count
FROM
a
GROUP BY
a.id
)
SELECT
x.id_count,
a.id,
a.score
FROM
a
INNER JOIN
x
ON
x.id = a.id
ORDER BY
a.id,
a.score
;
id_count id score
---------- ---------- ----------
3 2 10
3 2 30
3 2 30
2 3 20
2 3 20
1 5 10
Sub query:
SELECT
x.id_count,
a.id,
a.score
FROM
a
INNER JOIN
(SELECT
a.id,
COUNT(*) AS id_count
FROM
a
GROUP BY
a.id
) x
ON
x.id = a.id
ORDER BY
a.id,
a.score
;
Someone with greater knowledge than mine could probably provide a
correlated sub-query as well.
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
