March 13, 2007 at 5:21 am
hello everyone, here is my SP:
CREATE PROCEDURE ChartGetRegisteredUsers
AS
--Get Pending Distributor Count
SELECT count(DistributorID) as RegisteredDistributors
from Distributors
WHERE approvalstatuscode = 2
SELECT count(ResellerID) as RegisteredResellers
from Resellers
WHERE approvalstatuscode = 2
GO
it returns 2 tables, now i want to make a function and in that it
returns only 1 table with these 2 values of the count, plz tell me how i do this as thses both comes
from different tables, plz tell me who i write my query that retuns the 1 tabbe but 2 columns thats shows the
result of these 2 stms count. plz reply me . Thanx in advance.
March 13, 2007 at 5:46 am
A very simple way of doing this would be to declare two variables @RegisteredDistributors and @RegisteredResellers
Set the two variables to the counts and then select both back:
set nocount on
declare @RegisteredDistributors int, @RegisteredResellers int
SELECT @RegisteredDistributors = count(DistributorID)
from Distributors
WHERE approvalstatuscode = 2
SELECT @RegisteredResellers = count(ResellerID)
from Resellers
WHERE approvalstatuscode = 2
SEELECT @RegisteredDistributors as RegisteredDistributors,
@RegisteredResellers as RegisteredResellers
I'm sure there are other ways but this does the trick.
Hope this helps
S
March 13, 2007 at 5:53 am
hey i got it now u plz check it. is it fine or not?
CREATE PROCEDURE ChartGetRegisteredUsers
AS
SELECT count(DistributorID)
from Distributors
WHERE approvalstatuscode = 2 union all (SELECT count(ResellerID)
from Resellers
WHERE approvalstatuscode = 2)
GO
if any other good way to do that then let me know. me waiting for good reply.
Thanx in advance.
March 13, 2007 at 6:02 am
Um, your solution returns two rows of one column. I thought you wanted to get two columns on a single row.
S
March 13, 2007 at 11:41 am
In order to have a single row resultset and be able to distinguish between the values (i.e. different columns), something like this might work...
SELECT
SUM(Distributors) AS RegisteredDistributors,
SUM(Resellers) AS RegisteredResellers
FROM
(
SELECT
COUNT(DistributorID) AS Distributors,
0 AS Resellers
FROM Distributors
WHERE approvalstatuscode = 2
UNION ALL
SELECT
0 AS Distributors,
COUNT(ResellerID) AS Resellers
FROM Resellers
WHERE approvalstatuscode = 2
)
March 14, 2007 at 7:54 am
how about just
CREATE PROCEDURE ChartGetRegisteredUsers
AS
select (SELECT count(DistributorID) from Distributors WHERE approvalstatuscode = 2) as Distributors,
(SELECT count(ResellerID) from Resellers WHERE approvalstatuscode = 2) as Resellers
GO
March 15, 2007 at 12:32 am
Thanx R2ro for ur reply, but when i run ur query it gives me following error: incorrect syntax near ( plz tell me the correct one without error as i cant find the problem and y it giving the error on the end line.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply