Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

SQL Count

Hello,

I have table that contains

total      type
66      1
1560      3
6      5
130      2

The total column is derrived from a count / group by.

Basically I wish to ammend this so that it only counts certains types, how would I accomplish this for say type 1,3,4 so it would return

total      type
66      1
1560      3
0      4

Notice that it should return 0 where it does not find any, not miss the row off
0
andyw27
Asked:
andyw27
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
--put 1,3,4 into a temporary table.
select 1 type into #types

insert into #types select 3
insert into #types select 4


select isnull(total, 0) total, t.type
From table t
left join #types t2 on t.type = t2.Type
where t.type in (select type from #types)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
;WITH cnt (cType )  AS (
 SELECT 1  
 UNION ALL
 SELECT 3
 UNION ALL
 SELECT 4   )


 SELECT cType, SUM( ISNULL(total,0) )  AS Total  
 FROM UrTable u
 RIGHT JOIN cnt c ON u.typpe = c.cType  
 GROUP BY cType
0
 
andyw27Author Commented:
should note the total is not a column, but is derived from an count command, here is the query:

SELECT
COUNT(object_id) as total,
object_type
FROM
object_audit_info
WHERE
action_type = '1' and person_id = '123'
group by object_type

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
It's usually helpful if you post that kind of stuff in your question to begin with.

Here is my modified query:
select 1 type into #types
insert into #types select 3
insert into #types select 4


SELECT
isnull(COUNT(object_id), 0) as total,
object_type
FROM
object_audit_info i
left join #types t2 on i.object_type = t2.Type
WHERE
action_type = '1' and person_id = '123' and i.object_type in (select type from #types)
group by object_type

Open in new window

0
 
andyw27Author Commented:
Ok thanks, gave it a try but it does not return rows when count is zero
0
 
SharathData EngineerCommented:
try this.
 ;WITH CTE
     AS (SELECT 1 object_type
         UNION ALL
         SELECT 3
         UNION ALL
         SELECT 4)
SELECT ISNULL(t2.total, 0) total,t1.object_type
  FROM CTE t1
       LEFT JOIN (SELECT Count(object_id) AS total,object_type
                    FROM object_audit_info
                   WHERE action_type = '1'
                     AND person_id = '123'
                   GROUP BY object_type) t2
              ON t1.object_type = t2.object_type  

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
That should be a right join . . . my apologies.

Try the following:
select 1 type into #types
insert into #types select 3
insert into #types select 4

SELECT
isnull(COUNT(object_id), 0) as total,
object_type
FROM
object_audit_info i
RIGHT join #types t2 on i.object_type = t2.Type
WHERE
action_type = '1' and person_id = '123' 
group by object_type

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now