Groupby clause

[#1]

Hi All

can any one help me on this.

i want count(id)  ,name from table1,  join table2  on table1.rid=table2.sid  where vid=1 and ,tvid=1.group by name

please find the tables.

	Table1				table2
id	rid	vId			sid	name	TvId
202	1	1			1	car	1
203	2	1			2	truck	1
204	3	1			3	bus	1
205	4	1			4	train	1
206	4	1
207	3	1
208	2	1
209	1	1
210	1	1
211	1	1
212	1	1
213	2	1
214	3	1
215	4	1
216	4	1
217	3	1
218	2	1
219	1	1
220	1	1
221	2	1
222	2	1
223	3	1
224	3	1

thanks.

[Gysbert_Wassenaar · Accepted solution]

Rename rid to sid or sid to rid to associate your tables. Then reload your document. Next create a chart object, e.g. a straight table, and add name as dimension and count({<vid={1},tvid={1}>}id) as expression. Make sure to use the exact case sensitive field names of your document.

[#3]

Hi Gysbert Wassenaar

is it not possible to write it in load script.

[alexandros17]

here it is, let me know

t1:

LOAD * Inline [

id, rid, vId

202, 1, 1

203, 2, 1

204, 3, 1

205, 4, 1

206, 4, 1

207, 3, 1

208, 2, 1

209, 1, 1

210, 1, 1

211, 1, 1

212, 1, 1

213, 2, 1

214, 3, 1

215, 4, 1

216, 4, 1

217, 3, 1

218, 2, 1

219, 1, 1

220, 1, 1

221, 2, 1

222, 2, 1

223, 3, 1

224, 3, 1

];

t2:

LOAD * Inline [

sid, name, TvId

1, car, 1

2, truck, 1

3, bus, 1

4, train, 1

];

TTemp:

NoConcatenate

LOAD id, rid, vId Resident t1;

Join

LOAD sid as rid, name, TvId Resident t2;

DROP Tables t1, t2;

T:

NoConcatenate

LOAD count(id)  ,name Resident TTemp

where vId=1 and TvId=1

Group By name;

[Gysbert_Wassenaar]

Temp:

Load id, rid as sid, vid from source1;

join sid, name, tvid from source2;

Result:

load name, count(id) as count_of_id

resident Temp

where vid=1 and tvid=1

group by name;

Drop table Temp;

[Anonymous]

Table1:

LOAD

id,

rid as JoinId,

FROM <yourdatasource.table1>

where vld=1;

left join (Table1)

LOAD

name,

sid as JoinID

FROM <yourdatasource.table2>

where TvId=1;

Table3:

LOAD

name,

count(id) as Count

resident Table1

group by name;

drop tables Table1;