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?
..
LVL 1
WeTiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeTiAuthor Commented:
Yes, I agree, It would be better with 2 query instead everything in one. Thanks for the teaching.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.