| Help in framing sql query [message #573280] |
Wed, 26 December 2012 04:04  |
 |
rkrish
Messages: 1
Registered: December 2012
|
Junior Member |
|
|
Hi,
I have two tables svc_table and svc.
svc_table has following columns:
sbscrp_id,
svc_name,
svc_eff_dt,
svc_expr_dt
and svc has the following rows:
seq_nbr,
svc_p_expr_dt,
svc_P_eff_dt,
svc_name,
member_svc_name.
I need to frame a query using these two tables,
The query should return sbscrp_id from svc_table and count of seq_nbr's each sbscrp_id has from svc table.
I have framed a query like :
select distinct sbscrp_id ,seq_nbr from svc s,svc_table st where (s.svc_name = st.svc_name or s.member_svc_name= st.svc_name) ;
this will return sbscrp_id's and seq_nbr's they have but I need to fetch sbscrp_id's and count of seq_nbr's each have.
can any one help me in framing the query 
|
|
|
|
| Re: Help in framing sql query [message #573282 is a reply to message #573280] |
Wed, 26 December 2012 04:32  |
 |
Littlefoot
Messages: 16997
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You want to get "count", yet you didn't use COUNT function in your query. Why? What happens if you - instead of SEQ_NBR - put COUNT(*) and add the GROUP BY SBSCRP_ID clause? Also, in that case, remove DISTINCT keyword as you don't need it any more.
select sbscrp_id, count(*)
from svc s, svc_table st
where s.svc_name = st.svc_name
or s.member_svc_name = st.svc_name
group by sbscrp_id; |
|
|
|
] 
Members
Search
Help
Register
Login
Home
