how to implement Group by in Sub Query

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

Hi this is Ramesh.

My subQuery sp is

alter proc [dbo].[UDSP_GetPivotTable_Tmf_New_WithAllClients]          
(          
@ClientID int    
)          
as   
SELECT  distinct
        ( SELECT  COUNT(*)   FROM tbl_MainList WHERE  Code = 'Visit';    and ClientID=@ClientID  )   as Visit,
        (SELECT COUNT(*)  FROM tbl_MainList WHERE Code= 'Ptp'   and ClientID=@ClientID)as Ptp
from tbl_MainList where ClientID=@ClientID;

The Above SP Returns
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00 Ashish
7 4 6 27 9 379269.00 180283.00 93013.00 128875.00 71626.00 Ayyappa

But the Query returns all the records same but Different Employee Names.

How to pass in Employee name in procedure or Subquery.
suppose i am passing employee name in procedure it will execute only first statement.Second thing gives wrong.

Posted 29-Mar-12 20:08pm

ARK Kambhampati

Updated 29-Mar-12 21:21pm

Rajeev Jayaram

v2

Add a Solution

Comments

Saral S Stalin 30-Mar-12 2:15am

Can you post the table schema along with sample data and your required output? Subquery may not be the best option here.

Saral S Stalin 30-Mar-12 2:54am

I understand that this is your second question for the same thing. Abinav suggested to use PIVOT in the first question. Feel like you were not able to understand the PIVOT and use it. I have answered the orignial question with the script you need. Rather than raising a second question, you could have asked for more help in the first.

Rajeev Jayaram 30-Mar-12 3:22am

Added pre tag.

1 solution

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)

milenalukic · Answer 1 · 2012-03-30T11:10:00

Hi,

What you need to do is:

SQL

alter proc [dbo].[UDSP_GetPivotTable_Tmf_New_WithAllClients]          
(          
@ClientID int    
)          
as   

DECLARE @VISIT INT
DECLARE @PTP INT

SELECT @VISIT=SELECT  COUNT(*)   FROM tbl_MainList WHERE  Code = 'Visit' and ClientID=@ClientID

SELECT @PTP = SELECT COUNT(*)  FROM tbl_MainList WHERE Code= 'Ptp'   and ClientID=@ClientID

SELECT @VISIT AS VISIT, @PTP AS PTP