CODEWITHSUNDEEP
androidsqlitegroup-by
menasoft
menasoft

Reputation: 145

Get all rows from group by query

I have database sqlite contain 2 tables:

names

Table with two columns. The column id contains numbers and the column name contains strings.

n_data

Table with three columns. The column id contains numbers, the column name_id contains numbers, and the column value contains strings.

and query: 

select 
    n_data.id,n_data.value, count( n_data.id) as count 
from 
    n_data 
INNER JOIN names on names.id = n_data.name_id 
group by 
    n_data.name_id 
order by 
    n_data.id asc

In activity I have used Cursor and while 

 while (res.moveToNext()) {
      System.out.println("id=>"+res.getString(0)+" count=>"+res.getString(2)+" =value=>"+res.getString(1));
 }

but result just show last row in group. How can I get all rows for every group?

CREATE TABLE "names" (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,  
    name    TEXT );
INSERT INTO names (id,name) VALUES  
(1,'name_1'),  
(2,'name_2'),  
(3,'name_3'),  
(4,'name_4');  
CREATE TABLE "n_data" (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,  
    name_id TEXT,  
    value   TEXT );  
INSERT INTO n_data (id,name_id,value) VALUES  
(1,'3','value_8'),  
(2,'2','value_7'),  
(3,'2','value_6'),  
(4,'2','value_5'),  
(5,'1','value_4'),  
(6,'1','value_3'),  
(7,'1','value_2'),  
(8,'1','value_1'),  
(9,'3','value_9');

Upvotes: 0

Views: 1583

Answers (1)

Yunnosch
Yunnosch

Reputation: 26703

OP is satisfied by: 

select 
    n_data.id,
    group_concat(n_data.value) as 'all values',
    count( n_data.id) as count
from
    n_data INNER JOIN names 
    on names.id = n_data.name_id
group by n_data.name_id
order by n_data.id asc;

It uses group_concat(n_data.value) instead of n_data.value.
I.e. all the data.value which get counted by count(n_data.id) are concatenated.

Output (.headers on, .mode column and .width 3 32 6; SQLite 3.18.0 2017-03-28) : 

id   all values                        count
---  --------------------------------  ------
4    value_7,value_6,value_5           3
8    value_4,value_3,value_2,value_1   4
9    value_8,value_9                   2

The tailored .width is needed, otherwise for id 8, only 3 values are shown, though 4 are retrieved.

Upvotes: 1

Related Questions