Reusing views to do aggregate queries [closed]

Question

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?

Answer 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