Combining 2 queries in SQL 2008

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: Last updated:
Related Questions
1

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • DavidHa!!
    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
    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!!
    thanks Mariodlg, but no go. I have a solution to the original question but look at the next post for more...
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: