Count 3 Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count 3 Tables

Author

Topic

ramz003
Starting Member

3 Posts

Posted - 2011-11-03 : 10:44:32

I'm trying to get a count on three different tables that meet certain requirements. I get the totals but on multiple lines. Here is my code.

select
NVL((select projectname from xxx_sys_project_info where project_id = pid),'99 - null') as Project_Name,
Count(*) as RFIs,
TO_NUMBER (null) as Submittals,
TO_NUMBER (Null) as RFI
from xxx_ur2
GROUP BY PROJECT_ID
UNION
select
NVL((select projectname from xxx_sys_project_info where project_id = pid),'99 - null') as Project_Name,
TO_NUMBER (NULL) as RFIs,
Count(*) as Submittals,
TO_NUMBER (Null) as RFI
from xxx_us1
where status = 'In_Review' or status = 'Pending' or status = 'Revise_and_Resubmit'
GROUP BY project_id
UNION
select
NVL((select projectname from xxx_sys_project_info where project_id = pid),'99 - null') as Project_Name,
TO_NUMBER (NULL) as RFIs,
TO_NUMBER (NULL) as Submittals,
Count(*) as RFI
from xxx_ur4
where status = 'In_Review' or status = 'Pending' or status = 'Revise_and_Resubmit'
GROUP BY project_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 10:58:46

and how do you want it to appear?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 11:03:42

may be this


select Project_Name,SUM(RFIs),SUM(Submittals),SUM(RFI)
from
(
select 
NVL((select projectname from xxx_sys_project_info where project_id = pid),'99 - null') as Project_Name,
Count(*) as RFIs,
TO_NUMBER (null) as Submittals,
TO_NUMBER (Null) as RFI
from xxx_ur2
GROUP BY PROJECT_ID
UNION
select
NVL((select projectname from xxx_sys_project_info where project_id = pid),'99 - null') as Project_Name,
TO_NUMBER (NULL) as RFIs,
Count(*) as Submittals,
TO_NUMBER (Null) as RFI
from xxx_us1
where status = 'In_Review' or status = 'Pending' or status = 'Revise_and_Resubmit'
GROUP BY project_id
UNION
select
NVL((select projectname from xxx_sys_project_info where project_id = pid),'99 - null') as Project_Name,
TO_NUMBER (NULL) as RFIs,
TO_NUMBER (NULL) as Submittals,
Count(*) as RFI
from xxx_ur4
where status = 'In_Review' or status = 'Pending' or status = 'Revise_and_Resubmit'
GROUP BY project_id
)t
GROUP BY Project_Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ramz003
Starting Member

3 Posts

Posted - 2011-11-03 : 11:03:46

All on one line per project.

Project RFIs Sub RFI
1234 2 3 3

Right now I get:
Project RFIs Sub RFI
1234 2 0 0
1234 0 3 0
1234 0 0 3

ramz003
Starting Member

3 Posts

Posted - 2011-11-03 : 11:12:19

Thanks!! I had to modify it but it worked.

select Project_Name,SUM(RFIs) as RFIs,SUM(Submittals)as Submittals,SUM(RFI)as RFI
from

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 11:45:14

wc
make sure you try in oracle forums in future if you're using oracle
this is ms sql server forum so solutions given here are guaranteed to work only in sql server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources