Home > IT Answers > SQL Server > Combining 2 queries in SQL 2008
DavidHa!!
110 pts.
 Combining 2 queries in SQL 2008
SQL 2008, T-SQL 2008, SQL Server 2008, SQL Server administration, SQL Server Query
I am having a little trouble combining the following 2 queries into a single nested statement (if indeed it's possible). Can anyone assist?

Query 1:

SELECT Eff_Date, CoNum, Brandname, COUNT(Brandname) AS CountOfBrandName FROM dbo.Products WHERE (Eff_Date BETWEEN '1/1/2010' AND '12/31/2010') GROUP BY Eff_Date, CoNum, Brandname, DateApplied

Query 2:

SELECT Eff_Date, CoNum, SUM(CountOfBrandName) AS SUMofBrandNames FROM dbo.Query1 GROUP BY Eff_Date, CoNum ORDER BY Eff_Date

Can anyone assist in combining these 2 queries into a single nested statement? Can it be done without using a temp table?



Software/Hardware used:
SQL 2008
ASKED: Mar 4, 2011  3:34 PM GMT
UPDATED: March 4, 2011  7:20:38 PM GMT
RELATED QUESTIONS
BigKat
5,535 pts.
Answer Wiki:
with query1 as (SELECT Eff_Date, CoNum, Brandname, COUNT(Brandname) AS CountOfBrandName FROM dbo.Products WHERE (Eff_Date BETWEEN '1/1/2010' AND '12/31/2010') GROUP BY Eff_Date, CoNum, Brandname, DateApplied)
SELECT Eff_Date, CoNum, SUM(CountOfBrandName) AS SUMofBrandNames FROM Query1 GROUP BY Eff_Date, CoNum ORDER BY Eff_Date
Last Wiki Answer Submitted:  Mar 4, 2011  4:22 PM (GMT)  by  BigKat   5,535 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

Thanks BigKat..nice work. To take it one step further, how about the ability to produce a total number of conum’s and the sum of the Sumofbrandname columns. This would end up with including select count (conum), sum(sumofbrandnames) as the final result. Can this also be added to the above query?

DavidHa!!
 110 pts.
 

It seems to be very simple in SQL Server.
I tried to make a SQL standard query using subselect, in order to get the same results in another database manager.

Here it is:

select tmp.Eff_date, tmp.CoNum, sum(tmp.CountOfBrandname) from
( select Eff_date, CoNum, count(Brandname) as CountOfBrandname, Brandname, Dateapplied
from Products
where Eff_date between ‘01/02/2011′ and ‘05/02/2011′
group by Eff_date, CoNum, Brandname, Dateapplied) as tmp
group by tmp.Eff_date, tmp.CoNum;

Tell us if you get the desired results.
Regards.

Mariodlg
 2,790 pts.
 

thanks Mariodlg, but no go. I have a solution to the original question but look at the next post for more…

DavidHa!!
 110 pts.