CODEWITHSUNDEEP
sql-server-2005t-sqlcount
Sheik Syed Mohaideen
Sheik Syed Mohaideen

Reputation: 191

Use multiple counts in SQL Server 2005

select p.intprojectid, p.vcprojectname, md.intmoduleid, 
       md.vcmodulename, md.intscreensfunc, md.vcname 
from projects as p 
left join (select m.intprojectid, m.intmoduleid, m.vcmodulename, 
                  s.intscreensfunc, s.vcname 
           from modules as m 
           left join screens_func as s on m.intmoduleid = s.intmoduleid) md 
           on p.intprojectid = md.intprojectid

This query will return:

no |project-name|mod-id|mod-name  |  screen-id   |  screen-name
----------------------------------------------------------------
2   Project-1     4      mod-1          11            scr1
2   Project-1     4      mod-1          12            scr2
2   Project-1     4      mod-1          13            scr3
2   Project-1     4      mod-1          14            scr4
2   Project-1     8     Module-2       NULL           NULL

Now I want to count no.of mod-name and no.of.screen-name in project-1. i.e. I want the query to return 

project-name     no.of.mod          no.of.screen
------------------------------------------------
Project-1          2                    4

Upvotes: 0

Views: 211

Answers (1)

Code Magician
Code Magician

Reputation: 23972

It's definitely possible to return multiple counts.

In other words, your query could be modified as follows:

select p.vcprojectname, COUNT(DISTINCT md.intmoduleid) as no.of.mod, COUNT(md.intscreensfunc) as no.of.screen
from projects as p 
    left join (select m.intprojectid, m.intmoduleid, m.vcmodulename, s.intscreensfunc, s.vcname 
               from modules as m 
               left join screens_func as s 
                        on m.intmoduleid=s.intmoduleid)md 
           on p.intprojectid=md.intprojectid
GROUP BY p.vcprojectname

Based on your example data, I inferred that there would be a one-many relationship between modules and screens and thus you would want a distinct count for modules but that the same requirement would not be needed for screens (since it appears that one screen would not appear multiple times in a given module) If that is not the case, you can also add distinct to the count of screens.

Upvotes: 2

Related Questions