Subquery in select statement

I have this query that is giving me the correct results.
If I remove the  e.Country='JP' and Year(vm.ApplicationDate)=2012 from the where clause I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can I rewrite the query in order it to work?

SELECT 
	Year(vm.ApplicationDate) as [Year],	e.Country,
	count(*) AS [Direct app],
	(	SELECT
			Count(*) 
		FROM Product vm inner join	
			 Owners e on vm.ProductID=e.fkProductID inner join
			 ProductGroup fl on vm.VMID=fl.VMID 

			 where 		e.Country='JP' and Year(vm.ApplicationDate)=2012
   group by e.Country )as [Number of Cl]

FROM Product vm inner join
  Owners e on vm.ProductID=e.fkProductID 
where  e.Country='JP' and Year(vm.ApplicationDate)=2012
  
  group by e.Country
  ,Year(vm.ApplicationDate)
 

Open in new window

gosi75Business Intelligence ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Remove the GROUP BY from the subquery.  Without the WHERE clause you would be returning a count for each country in the Owners table.
UnifiedISCommented:
Join the subquery (instead of using as expression) on the columns in the where clause

SELECT
      Year(vm.ApplicationDate) as [Year],      
      e.Country,
      count(*) AS [Direct app],
      [Number of Cl].AppCount

FROM Product vm inner join
  Owners e on vm.ProductID=e.fkProductID
INNER JOIN (      SELECT
                  Count(*) As AppCount ,
                  e.Country,
                  YEAR(vm.ApplicationDate) As AppYear
            FROM Product vm inner join      
                   Owners e on vm.ProductID=e.fkProductID inner join
                   ProductGroup fl on vm.VMID=fl.VMID
                                     
   group by e.Country, vm.ApplicationDate )as [Number of Cl]  
   ON [Number of Cl].Country = e.Country
   AND [Number of Cl].AppYear = Year(vm.ApplicationDate)
where  e.Country='JP' and Year(vm.ApplicationDate)=2012
    group by e.Country
  ,Year(vm.ApplicationDate),
  [Number of Cl].AppCount

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
try:
  SELECT 
	Year(vm.ApplicationDate) as [Year]
	, e.Country
	, d.Qty AS [Direct app],
	From (SELECT
		Count(*) AS Qty 
		FROM Product vm inner join	
			 Owners e on vm.ProductID=e.fkProductID inner join
			 ProductGroup fl on vm.VMID=fl.VMID 
			 where 	e.Country='JP' and Year(vm.ApplicationDate)=2012
       group by e.Country )as [Number of Cl]) AS d
  group by e.Country
  , Year(vm.ApplicationDate)

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim HornMicrosoft SQL Server Data DudeCommented:
Just for kicks and giggles, execute the subquery by itself without the WHERE clause, and tell us what the return set.   I would think that it would only return one value.

SELECT Count(*) 
FROM Product vm 
   inner join Owners e on vm.ProductID=e.fkProductID 
   inner join ProductGroup fl on vm.VMID=fl.VMID 

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
You have used a multi valued correlated subquery in SELECT clause which is not allowed because it returns multiple values as you have stated so. Correlated subqueries (scalar or multi-valued are allowed in WHERE clause. You can used derived tables subquery instead (which comes in FROM clause).  Above, I have attempted to use derived table subquery but it needs some additional work I will try to fix it.

Mike

 SELECT 
	d.[Direct app]
	, Year(vm.ApplicationDate) as [Year]
	, d.Country
	From (SELECT
		Count(*) AS [Number of Cl]
		, e.Country 
		FROM Owners e  inner join
			 ProductGroup fl on vm.VMID=fl.VMID
		where vm.ProductID = e.fkProductID And e.Country='JP' 
        group by e.Country) AS d ([Number of Cl], Country)
  From Product vm 
  WHERE Year(vm.ApplicationDate)=2012

Open in new window


It is not clear what information you want from table ProductGroup.

This post has been revised...
Vitor MontalvãoMSSQL Senior EngineerCommented:
gosi75, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.