CODEWITHSUNDEEP
sqlsql-server
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

SQL: Sub Query to filter the result of a Query

SELECT SUM(imps.[Count]) AS A , COUNT (imps.Interest_Name) AS B, ver.Vertical_name
FROM Impressions imps
INNER JOIN Verticals ver 
ON imps.Campaign_id = ver.Campaign_id
WHERE ver.Vertical_name = 'Retail' OR ver.Vertical_name = 'Travel'
GROUP BY imps.Interest_Name, ver.Vertical_name;

The above query returns a record as :

 A  B   Vertical_name
 6  6   Retail
 3  2   Retail
 7  1   Travel
13  10  Travel

I want to modify this query to get a result such as :

 A  B   Vertical_name
 9  8   Retail
 20 11  Travel

That is further grouping by the vertical name and taking the SUM of the colums A and B. I guess this has to be done by a sub query buy not sure how?

Upvotes: 1

Views: 60

Answers (3)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

try this query

SELECT SUM(imps.[Count]) AS A , COUNT (imps.Interest_Name) AS B, ver.Vertical_name
FROM Impressions imps
INNER JOIN Verticals ver 
ON imps.Campaign_id = ver.Campaign_id
WHERE ver.Vertical_name = 'Retail' OR ver.Vertical_name = 'Travel'
GROUP BY  ver.Vertical_name;

Upvotes: 1

Venkatesh Panabaka
Venkatesh Panabaka

Reputation: 2154

your doing group by two column i.e imps.Interest_Name, ver.Vertical_name. I modified your SQL i think these will useful to you.

SELECT SUM(imps.[Count]) AS A , COUNT (imps.Interest_Name) AS B, ver.Vertical_name
    FROM Impressions imps
    INNER JOIN Verticals ver 
    ON imps.Campaign_id = ver.Campaign_id
    WHERE ver.Vertical_name = 'Retail' OR ver.Vertical_name = 'Travel'
    GROUP BY  ver.Vertical_name;

Thank you..

Upvotes: 1

ughai
ughai

Reputation: 9880

Just group by vertical_name and remove the imps.Interest_Name from your GROUP BY since you doing a COUNT (imps.Interest_Name) on it.

SELECT SUM(imps.[Count]) AS A , COUNT (imps.Interest_Name) AS B, ver.Vertical_name
FROM Impressions imps
INNER JOIN Verticals ver 
ON imps.Campaign_id = ver.Campaign_id
WHERE ver.Vertical_name = 'Retail' OR ver.Vertical_name = 'Travel'
GROUP BY ver.Vertical_name;

Upvotes: 2

Related Questions