• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 44
  • Last Modified:

Improve query and sub query ) As

Dear expert

Question of more of this query:

select
        Person
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [First]
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [Second]
from (
    select
          person
        , row_number() over(order by person) as rn
    from persons
     ) as sq
order by rn

Open in new window

Output would be:
person      First   Second
Barney      1        2
Betty         NULL  NULL
Fred          NULL NULL
Wilma

Now the person column is showing up, no problem ok now what do I do if I want to add one more column with more results like column Salary, that returns 10 rows?
..
0
WeTi
Asked:
WeTi
  • 2
  • 2
1 Solution
 
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Is salary available in persons table or you need to get it from other table. ?
0
 
WeTiAuthor Commented:
this is just a case for my learning of sql query, so senario1 its in another table name, info_membership. senario2 its in same table as person.
0
 
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
If you are exploring how to write sql queries. Approach you have used to get count is not good. Because let say, if you will have 1000 rows in person table you will get 1000 rows having First and second column as null except first row and also it will take time to execute.

It's not efficient. If you need summarized value and detail value it's good to run two different queries.

Now answering your question.

If you want to introduce Salary in the query from person table, you can add column like in query.

select
        Person
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [First]
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [Second]
from (
    select
          person
,Salary
        , row_number() over(order by person) as rn
    from persons
     ) as sq
order by rn

Open in new window


If it's a join with other table. if can be like. Assuming "person" is available in both column.

select
        Person
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [First]
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [Second]
from (
    select
          person
,info.salary
        , row_number() over(order by person) as rn
    from persons p
INNER JOIN info_membership info
ON info.person=p.person
     ) as sq
order by rn
0
 
WeTiAuthor Commented:
Yes, I agree, It would be better with 2 query instead everything in one. Thanks for the teaching.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now