0

I have created some views which give me some values.

So some thing like the following

select from a table of all ids gives me some thing like

id1
id2
id3
id4

view1 gives me

id1 2
id1 1
id1 3
id2 1 

view2 gives me

id3 London
id3 Paris
id1 London

What I need is some thing like

id1  6  1
id2  1  0
id3  0  2
id4  0  0

As you can see that I need aggregation of numbers from first view and aggregation of number os cities from second view. What would be a way to do this?

2
  • You should probably explain how you arrive at the numbers in your desired result. Why, for example, you have id1 6 0 and not id1 6 1, or id1 1 6?
    – mustaccio
    Feb 25 '15 at 12:44
  • Thank, there is a bug in my question. id1 has three values in view 1 that get added to value of 6, and it has one city that should get result of 1
    – geoaxis
    Feb 25 '15 at 12:47
1

Following sample may be useful to build query which you want to write.

USE tempdb
GO

CREATE TABLE t_Ids (id VARCHAR(10) PRIMARY KEY )
GO

INSERT INTO t_Ids VALUES('id1'),('id2'),('id3'),('id4')
GO

CREATE VIEW v_V1
AS 
SELECT 'id1' id, 2 value UNION
SELECT 'id1' id, 1 UNION
SELECT 'id1' id, 3 UNION
SELECT 'id2' id, 1 
GO

CREATE VIEW v_V2
AS 
SELECT 'id3' id, 'London' city UNION
SELECT 'id3' id, 'Paris' UNION
SELECT 'id1' id, 'London' 
GO

SELECT t.id, ISNULL(SUM(v1.value), 0) AS agg_value,  COUNT(DISTINCT v2.city) cnt_city
FROM t_Ids AS t
LEFT OUTER JOIN v_v1 AS v1 ON t.id = v1.id
LEFT OUTER JOIN v_v2 AS v2 ON t.id = v2.id  
GROUP BY t.id
GO

DROP TABLE t_Ids
GO

DROP VIEW v_v1, v_v2
GO
1
  • thank you, what i really was looking for was left outer join and ISNULL(SUM(v1.value) , i used NVL in oracle.
    – geoaxis
    Feb 25 '15 at 13:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.