I am pretty new to this forum and Tera Data. I got a problem while writing a query.Please help me to sort this out.
I have three tables SUB_RAT,SUB_AGE,SUB_GEN. In three tables i have a common columns RAT_EN.And SUB_COUNT . I have to Select the Distinct RAT_EN from Each table(As three tables have same values in RAT_EN) and its corrosponding MAX VALUE from Sub_COUNT1,Sub_COUNT2,Sub_COUNT3.
RAT_EN SUB_COUNT1 SUB_COUNT2 SUB_COUNT3 car 1000 1500 1487 jeep 650 800 478 bus 4210 414 4514
Hope you understand my question... If any one want more clarification please ask.
I tried With the below Query. But it was showing some error "Syntax error: expected something between '(' and the 'SELECT' keyword." I am using a Tera DBMS
(SELECT max(sub_count) FROM UP_AALZEER.PS_V_SUB_RAT WHERE RAT_EN=A.RAT_EN) AS SUB_COUNT1,
(SELECT max(sub_count) FROM UP_AALZEER.PS_V_SUB_AGE WHERE RAT_EN=A.RAT_EN) AS SUB_COUNT2,
(SELECT max(sub_count) FROM UP_AALZEER.PS_V_SUB_GEN WHERE RAT_EN=A.RAT_EN) AS SUB_COUNT3
I think the below query solves your problem SELECT A.RAT_EN,MAX(A.sub_count),MAX(B.sub_count),MAX(C.sub_count) FROM UP_AALZEER.PS_V_SUB_AGE A, UP_AALZEER.PS_V_SUB_AGE B, UP_AALZEER.PS_V_SUB_AGE C WHERE A.RAT_EN = B.RAT_EN AND A.RAT_EN = C.RAT_EN GROUP BY A.RAT_EN;
Here is what i understand of the problem. You have three tables each having one common column(rat_en) and there are count in another column i.e. sub_count. Now we need to show the max(sub_count) for each of rat_en and from the three tables. here is queries that i ran...
create volatile table t3 ( rate_en char(20), sub_count integer) on commit preserve rows
the above three tables represent your tables and next step is to insert some data in it.. insert into t1 values('Car',100); insert into t1 values('Car',200); insert into t2 values('Car',200); insert into t2 values('Car',300); insert into t3 values('Car',300); insert into t1 values('Car',400);
Now we need to show the max of sub_count for 'Car' so writing three queries will do sel rate_en , max(sub_count) from t1 group by rate_en; sel rate_en , max(sub_count) from t1 group by rate_en; sel rate_en , max(sub_count) from t1 group by rate_en;
But since you need the output in a single row hence the below query should do
SELECT A.RATe_EN,MAX(A.sub_count),MAX(B.sub_count),MAX(C.sub_count) FROM t1 A, t2 B, t3 C WHERE A.RATe_EN = B.RATe_EN AND A.RATe_EN = C.RATe_EN GROUP BY A.RATe_EN;
rate_en MAX(A.sub_count) MAX(b.sub_count) MAX(c.sub_count) Car 200 300 400
going by your response earlier i think i am not understanding your requirements. Could you give us table structure and few records of your table and the answerset as you want?
i had created 3 tables t1,t2,t3 and inserted data given by you The following query gives you the desired result:
sel a.re,sc1,sc2,sc3 from (sel rat_en,max(sub_count) from t1 group by 1)a(re,sc1) inner join (sel rat_en,max(sub_count) from t2 group by 1) b (re,sc2) on a.re=b.re inner join (sel rat_en,max(sub_count) from t3 group by 1) c (re,sc3) on a.re=c.re