using two count function

Question

3.00/5 (4 votes)

See more:

Hi
I am trying to use two count function in one select statement in stored procedure but doesn't works

SQL

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

kolisa

Updated 3-Jul-12 3:48am

Tim Corey

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mohamed Mitwalli · Accepted Answer · 2012-07-03T04:51:00

Solution 1

Hi
Check this

SQL

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

Posted 3-Jul-12 4:51am

Mohamed Mitwalli

Comments

kolisa 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

Bernhard Hiller · Accepted Answer · 2012-07-03T20:54:00

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