Combining 2 queries in SQL 2008

DavidHa!!

110 pts.

Tags:

SQL 2008

SQL Server 2008

SQL Server administration

SQL Server Query

T-SQL 2008

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: March 4, 2011 3:34 PM Last updated: March 4, 2011 7:20 PM

Answer Wiki

Last updated: March 4, 2011 4:22 PM GMT

BigKat9,110 pts.
History
Contributors
- Ordered by most recent
- BigKat9,110 pts.

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 3 Replies

DavidHa!! Mar 4, 2011 5:49 PM GMT

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?

0 pointsBadges:

report
Mariodlg Mar 4, 2011 6:08 PM GMT

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.

2,790 pointsBadges:

report
DavidHa!! Mar 4, 2011 7:20 PM GMT

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

0 pointsBadges:

report