Counts, join and different criteria in same query

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

SQL Server 2008 Forums

Transact-SQL (2008)

Counts, join and different criteria in same query

Author

Topic

Pedrito128
Starting Member

3 Posts

Posted - 2011-03-31 : 10:59:24

Hi! My name is Pedrito and I am newbie in the forum.
I need help with a query with two counts from
two tables in a left outer join.

I have this two tables related by Zone.id ->Data.id_zone

Zone
=================
id | zone_name
-----------------
1 | Zone1
2 | Zone2
3 | Zone3

Data
==========================================
id | id_zone | name | def
------------------------------------------
1 | 1 | Alan | 01/24/2010
2 | 3 | Peter | 02/15/2011
3 | 1 | Gregory | 01/01/2010

This is the desired result:

zone_name | 2010 | 2011
==== | ==== | ====
Zone1 | 6 | 2
Zone2 | 3 | 1
Zone3 | 0 | 3

The zone must to be ordered by its id field.

And this is my attempt. Thanks if you can help me and sorry for my english :(

SELECT Zone.id_AO, COUNT(Data.id) as total
FROM Zone
left outer join Data
ON Data.zone = Zone.id_AO
AND
(Data.def) between '01/01/2010' AND '31/01/2010'

GROUP BY Zone.id_AO
ORDER BY Zone.id_AO

union all

SELECT Zone.id_AO, COUNT(Data.id) as total
FROM Zone
left outer join Data
ON Data.zone = Zone.id_AO
AND
(Data.def) between '01/01/2011' AND '31/01/2011'

GROUP BY Zone.id_AO
ORDER BY Zone.id_AO

Pedrito

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-04-06 : 18:51:30

This will work I think

select z.zone_name,[2010],[2011]
from
(
select d.id_zone,
sum(case when d.def between 'jan 01,10' and 'dec 31,10' then 1 else 0 end)) as [2010],
sum(case when d.def between 'jan 01,11' and 'dec 31,11' then 1 else 0 end)) as [2011]
from data d
group by d.id_zone
) a
join zones z on z.id = d.id_zone
order by z.id

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-04-06 : 18:52:28

join zones z on z.id = d.id_zone
should be
join zones z on z.id = a.id_zone

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-07 : 02:33:24

I you nee to adding rows in Zone table that no matching with Data table then you need to left/right outer join like this:


SELECT zone_name, 
       COALESCE(cnt1, 0) AS [2010],
       COALESCE(cnt2, 0) AS [2011]
  FROM (SELECT id_zone,
               COUNT(CASE WHEN YEAR(def) = 2010 THEN 1 END) AS cnt1,
               COUNT(CASE WHEN YEAR(def) = 2011 THEN 1 END) AS cnt2
          FROM Data
         GROUP BY id_zone
        ) AS D
        RIGHT OUTER JOIN Zone AS Z
                      ON D.zone_id = Z.id;

______________________

Pedrito128
Starting Member

3 Posts

Posted - 2011-04-07 : 13:17:31

wwowwww!! Amazing!! I need to study this script! haha Really thanks! You guys rocks!!

Pedrito128
Starting Member

3 Posts

Posted - 2011-04-07 : 13:37:04

The ms65g solution is exactly what i need :), i think that pduffin solution was very close (near) :)

thanks!

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 13:45:43

quote:
Originally posted by Pedrito128

The ms65g solution is exactly what i need :), i think that pduffin solution was very close (near) :)

thanks!

you are welcome

______________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources