CODEWITHSUNDEEP
sqlsql-server-2008
OnceorTwice
OnceorTwice

Reputation: 49

How can I count and show the number of times a row appears within multiple joined tables?

I wrote the below query which shows me ApplicationIDs associated with two specific ables. I need the results to return the number of times each Applications.AppID appears in those tables next to the row with the application name. Ive used distinct because in my results I only want the name to appear once but have a number next to it indicating how many times it has been used. Examples below. Ive written count conditions before but only for single tables.

  SELECT 0 AppId   ,
         'Select an Application' ApplicationName
union all
  select .1        ,
         '--All--'
union all
  SELECT DISTINCT
         Applications.AppId ,
         Applications.ApplicationName
  FROM ImpactedApplications ,
       SupportingApplications 
  JOIN applications              ON SupportingApplications.Appid = applications.appid
  JOIN ImpactedApplications Apps on SupportingApplications.AppId = Applications.AppId

Returns something like this:

0.0   Select an Application
0.1   --All--
12.0  APP A
59.0  APP B
60.0  APP C
71.0  APP D
74.0  APP E
121.0 APP F
124.0 APP G
130.0 APP H

I want it to return something like this:

0.0   Select an Application
0.1   --All--
12.0  APP A 1
59.0  APP B 2
60.0  APP C 1
71.0  APP D 4
74.0  APP E 3
121.0 APP F 1
124.0 APP G 2
130.0 APP H 2

Any help is appreciated thank you.

Adding Results from Help Query

12 APP A 17161
59 APP B 51483
60 APP C 85805
71 APP D 17161

Upvotes: 1

Views: 106

Answers (3)

Nicholas Carey
Nicholas Carey

Reputation: 74277

First, you do realize that order is unspecified unless you order the result set using order by? That means there is no guarantee that the first two selects in your union all will come first.

So, let's strip those two out as they are really extraneous to the actual problem. Let us consider the core select:

select distinct Applications.AppId , Applications.ApplicationName from ImpactedApplications , SupportingApplications JOIN applications ON SupportingApplications.Appid = applications.appid JOIN ImpactedApplications Apps on SupportingApplications.AppId = Applications.AppId

and dissect it.

Problem #1.
select distinct is often a code smell indicating that you don't have the correct join criteria or you don't correclty understand the cardinality of the relationships involved.

Problem #2.
Indeed, this is the case. You are mixing old-school, pre-ISO/ANSI joins with ISO/ANSI joins. Since the first two tables in the FROM clause are joined pre-ISO/ANSI style, and you have no where clause with criteria to join them, The above select statement is exactly identical to

select distinct a.AppId , a.ApplicationName from ImpactedApplications ia cross join SupportingApplications sa join applications a on sa.Appid = a.appid join ImpactedApplications Apps on sa.AppId = a.AppId

I'm pretty sure you didn't intend to generate the cartesian product of the 2 tables. You haven't described the table schema, but my suspicion, from your problem statement

I need the results to return the number of times each Applications.AppID appears in those tables next to the row with the application name.

is that you want something more along these lines:

select AppId           = a.AppId           ,
       AppName         = a.ApplicationName ,
       ImpactedCount   = coalesce( ia.Cnt , 0 ) ,
       SupportingCount = coalesce( sa.Cnt , 0 ) ,
       Total           = coalesce( ia.Cnt , 0 )
                       + coalesce( sa.Cnt , 0 )
from      Applications           a
left join ( select AppId = t.AppId ,
                   Cnt   = count(*)
            from ImpactedApplications t
            group by t.AppId
          ) ia on ia.AppId = a.AppId
left join ( select AppId = t.AppId ,
                   Cnt   = count(*)
            from SupportingApplications t
            group by t.AppId
          ) sa on sa.AppId = a.AppId

If you want to restrict the results to just those rows with non-zero values, you could change the left join clauses to join, but that would mean you would only get those rows that have a non zero value for both. Instead, add a where clause to restrict the result set:

where sa.Cnt > 0
   OR ia.Cnt > 0

In addition to filtering out any rows where both counts are zero, it also removes rows where both rows have a null count, indicating that no match occurred in the left join.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

DISTINCT is logically equivalent to a GROUP BY:

SELECT Applications.AppId, Applications.ApplicationName
   ,COUNT(*)
FROM SupportingApplications 
INNER JOIN    applications ON SupportingApplications.Appid = applications.appid
INNER JOIN ImpactedApplications as Apps on SupportingApplications.AppId = Applications.AppId 
GROUP BY Applications.AppId, Applications.ApplicationName

Upvotes: 1

CSharper
CSharper

Reputation: 5580

SELECT 0 AppId, 'Select an Application' ApplicationName union all
                                select .1,'--All--' union all
SELECT DISTINCT app.AppId, app.ApplicationName,count(app.AppId)
FROM  SupportingApplications sa 
INNER JOIN    applications app ON sa.Appid = applications.appid
--INNER JOIN ImpactedApplications as Apps on sa.AppId = app.AppId
group by app.AppId, app.ApplicationName

It doesn't look like your doing anything with ImpactedApplications table, so idk maybe remove that line

Upvotes: 0

Related Questions