How to get the output

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

I have 2 table with data like this

Table1:

ID IP Account
1 172.22.22 SSIN
2 172.34.43 SSIN
3 1832.2.2.3 JJIN

Table2:

ID Account AccountName Flag Frequency
1 SSIN Healthy 1 15
2 SSIN Good 1 15
3 SSIN bad 1 15
4 JJIN Healthy 1 15
5 JJIN Good 1 15
6 JJIN bad 0 15

I want the output like

OutPut:

Account IP AccountName
SSIN 2 3
JJIN 1 2

Here the ip is the count of the account, and the accountname is also the count of account and checking with flag=1

Posted 14-Nov-11 23:38pm

inayat basha

Updated 14-Nov-11 23:57pm

Pandya Anil

v2

Add a Solution

Comments

Shabana Parveen 15-Nov-11 6:01am

What exactly you want, SQL/LINQ query to display the view?

inayat basha 15-Nov-11 6:02am

sql query to display

Shabana Parveen 15-Nov-11 6:05am

Are you sure JJIN 1 2 not JJIN 3 2?

inayat basha 15-Nov-11 6:07am

JJIN count is 1 from table1 and 2 from Table2

Shabana Parveen 15-Nov-11 6:09am

oops...

inayat basha 15-Nov-11 6:09am

what happen???

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

palraj001 · Answer 1 · 2011-11-15T07:02:00

Hi, We can Get that output from his join query,

SQL

SELECT   distinct   table2.Account,count(distinct(table1.IP)) as IP,  count(distinct(table2.AccountName)) as AccountName
FROM         table2 inner JOIN
                      table1 ON table1.Account = table2.Account where table2.Flag=1
group by   table2.Account ORDER BY table2.Account DESC

Regards,
Pal

sachin10d · Answer 2 · 2011-11-15T08:27:00

Solution 3

Try this

SQL

select t1.Account, t1.IP, t2.AccountName from
(select count(*) IP,Account from Table1 group by Account)t1
Inner Join
(select count(*) AccountName, Account from Table2 group by Account)t2
on t1.Account = t2.Account

Posted 15-Nov-11 8:27am

sachin10d

Updated 15-Nov-11 8:29am

v2

Manfred Rudolf Bihy · Answer 3 · 2011-11-15T03:41:00

This T-SQL statement does exactly what you are after:

SQL

SELECT DISTINCT t2.Account, 
    (SELECT COUNT(DISTINCT t1.IP) 
     FROM Table1 t1
     WHERE t1.Account = t2.Account) as IPCount,
    (SELECT COUNT(DISTINCT t22.AccountName)
     FROM Table2 t22
     WHERE t22.Account = t2.Account AND t22.Flag = 1) as AccountNameCount       
FROM Table2 t2
ORDER BY t2.Account DESC

Best Regards,

—MRB

Amir Mahfoozi · Answer 4 · 2011-11-15T18:23:00

Also consider this one :

SQL

select a.Account, IP, AccountName from 
   (select Account, count(account) IP from table1 group by Account) a
left outer join
   (select Account, count(account) AccountName from table2 where flag=1 group by Account) b
on a.Account=b.Account

Attention that the join type is important when some accounts have records in Table2 and doesn't have any in Tabl1.

Hope It Helps.

How to get the output

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0