Mutiple count queries with group by and where clause

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Posted 16-Jul-13 23:08pm

asassss

Add a Solution

3 solutions

Solution 3

SQL

SELECT
    country,
    COUNT(CASE
            WHEN entrydate >= '2001-01-01' AND entrydate < '2013-08-01'AND valid<>1
                THEN entrydate
            ELSE NULL
            END) AS [NV] ,
    COUNT(CASE
            WHEN entrydate >= '2001-06-01' AND entrydate < '2013-08-01'
                THEN entrydate
            ELSE NULL
            END) AS [RC]
FROM candidate_details
GROUP BY country

--Second Solution

SQL

SELECT
    country,
    CAST(
    COUNT(CASE
            WHEN entrydate >= '2001-01-01' AND entrydate < '2013-08-01'AND valid<>1
                THEN entrydate
            ELSE NULL
            END) AS VarCHAR(3)) + '|' +
            CAST(
    COUNT(CASE
            WHEN entrydate >= '2001-06-01' AND entrydate < '2013-08-01'
                THEN entrydate
            ELSE NULL
            END) AS VarCHAR(3)) 'NV/RC'
FROM candidate_details
GROUP BY country

Posted 16-Jul-13 23:43pm

Vehbi Neziri

Updated 16-Jul-13 23:49pm

v2

Solution 4

Select a.country,b.Nonvalidated,a.Registered_Candidates,c.Validated From
(select country,COUNT(*) as Registered_Candidates
from candidate_details
where entrydate between '2001-07-01' and '2013-08-01'
group by country)a
Join
(select country,COUNT(valid) as NonValidated
from candidate_details
where valid <>1 and entrydate between '2001-07-01' and '2013-08-01'
group by country
)b
on a.country=b.country
left join
(
select country,ISNULL(COUNT(valid),0) as Validated
from candidate_details
where valid = 1 and entrydate between '2001-07-01' and '2013-07-16'
group by country
)c
on a.country = c.country

country Nonvalidated Registered_Candidates Validated
Algeria 1 1 NULL
Australia 1 1 NULL
Bangladesh 1 1 NULL
Croatia 1 1 NULL
Egypt 1 1 NULL
Germany 1 1 NULL
India 33 38 5
New Zealand 1 1 NULL
Pakistan 1 1 NULL
Poland 1 1 NULL
Russia 1 1 NULL
Ukraine 1 1 NULL
Yugoslavia 2 2 NULL

How to show '0' instead of Null.????????

Posted 17-Jul-13 0:12am

asassss

Comments

asassss 17-Jul-13 6:42am

I got the ans of this above question:
soln: ISNULL(c.Validated,0)

Add a Solution

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

Raja Sekhar S · Accepted Answer · 2013-07-16T23:32:00

Solution 2

Try This..

SQL

Select a.country,b.Nonvalidated,a.Registered_Candidates From
(select country,COUNT(*) as Registered_Candidates
from candidate_details
where entrydate between '2001-07-01' and '2013-07-16'
group by country)a
Join
(select country,COUNT(valid) as NonValidated
from candidate_details
where valid <>1 and entrydate between '2001-07-01' and '2013-07-16'
group by country
)b
on a.country=b.country

Posted 16-Jul-13 23:32pm

Raja Sekhar S

Comments

asassss 17-Jul-13 5:44am

Thanks man.. its working..

Raja Sekhar S 17-Jul-13 5:46am

You are welcome......

Mutiple count queries with group by and where clause

3 solutions

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Mutiple count queries with group by and where clause

3 solutions

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us