Home » SQL & PL/SQL » SQL & PL/SQL » Help in framing sql query
Help in framing sql query [message #573280] Wed, 26 December 2012 04:04 Go to next message
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 Sad
Re: Help in framing sql query [message #573282 is a reply to message #573280] Wed, 26 December 2012 04:32 Go to previous message
Littlefoot
Messages: 19711
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;
Previous Topic: date query issue
Next Topic: SQL UPDATION
Goto Forum:
  


Current Time: Thu Oct 30 11:48:50 CDT 2014

Total time taken to generate the page: 0.11436 seconds