Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi
 
I am trying to use two count function in one select statement in stored procedure but doesn't works
SELECT DISTINCT COUNT(order),count(Cus),name
FROM MEd inner join Portfolios on MEd .MEd ID = Port.MEd Ref
INNER JOIN Pro on Port.portID = Pro.portRef
INNER JOIN P on Pro.proID = P.proRef
WHERE Port.MEd = @ID
GROUP BY name
Posted 3-Jul-12 3:47am
kolisa525
Edited 3-Jul-12 3:48am
Tim Corey88.4K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi
Check this
SELECT DISTINCT COUNT([order]),count(Cus),name
FROM MEd inner join Portfolios on MEd .MEd ID = Port.MEd Ref
INNER JOIN Pro on Port.portID = Pro.portRef
INNER JOIN P on Pro.proID = P.proRef
WHERE Port.MEd = @ID
GROUP BY name
Best Regards
M.Mitwalli
  Permalink  
Comments
kolisa at 4-Jul-12 2:13am
   
left join issues on Pro.proID = issues.projectRef
left join Risks on Pro.proID = Risks.projectRef
this two functions calculate field from this 2 tables,how can i solve that
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

There are several issues:
The DISTINCT key word is wrong, remove it.
If a column name contains blanks, you have to "escape" that name by using [square brackets], e.g. [MEd Ref].
Do not add blanks between table name and column name, e.g. use MEd.[MEd ID] instead of MEd .MEd ID!
ORDER is a reserved word and must be escaped.
I suggest to give the COUNT columns names, e.g.
SELECT COUNT([order]) as OrderCount, COUNT(Cus) as CusCount, name
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 4 Jul 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100