counting total rows when using GROUP BY

hi,

i have a stored procedure

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisitFROM UserVisitsWHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)GROUP BY UserNameUNIONSELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisitFROM UserVisitsAS UserVisits_1WHERE (ProductID = @.ProductID)AND (UserNameISNULL)GROUP BY AnonimIP

this will return something like:

zuperboy90 - 4 visits

ANONIMOUS - 6 visits

85.104.103 - 2 visits etc

how can i count the rows returned in both selections (4+6+2 = 12) ?

thank you

Put your whole query as a sub query then get the sum of TotalVisits.(4+6+12) as it is in your example

Select SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

|||

hi.

thanks for code, but if i write this i'll lost the other columns "TotalVisit" and "Visitor" :(

|||

Try this:

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisits

WHERE(ProductID= @.ProductID)AND(AnonimIPISNULL)

GROUPBY UserName

UNION ALL

SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_1

WHERE(ProductID= @.ProductID)AND(UserNameISNULL)

GROUPBY AnonimIP

SELECT'Total'AS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_2

WHERE(ProductID= @.ProductID)

|||

Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

Of course, if I decided to give you a username of 101.123.5.15, you would think I was an IP address.

You might want to try this instead:

Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP

) x

|||

hi

david wendelken:

Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

i get this error:Column 'x.Visitor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

david wendelken:

Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP

) x

i get this error:Each GROUP BY expression must contain at least one column that is not outer reference

i am sorry i don't know sql at all:(

|||

hi

jogi:

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisits

WHERE(ProductID= @.ProductID)AND(AnonimIPISNULL)

GROUPBY UserName

UNION ALL

SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_1

WHERE(ProductID= @.ProductID)AND(UserNameISNULL)

GROUPBY AnonimIP

SELECT'Total'AS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_2

WHERE(ProductID= @.ProductID)

i get this warrning:Error in GROUP BY clause.
Error in list of function arguments: 'AS' not recognized.
Unable to parse query text.

|||Sorry, it appears that I didn't make a full copy of the original statement.

Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

GROUP BY Visitor

Of course, if I decided to give you a username of 101.123.5.15, you would think I was an IP address.

You might want to try this instead:

Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP

) x

Group by Visitor

|||

hi, thanks for code

that stored procedure returns exacly what is in the SELECT clause: :(

127.0.0.124zuperboy903Test1@.yahoo.com1test2@.yahoo.com2

to simplify the problem, i will make a separate stored procedure for storing the total comments...i don't thnik is big deal another comand to database

thanks

|||

Glad that worked for you! Be sure to mark which answer(s) worked, so others will know too!

There is a general lesson to learn here about sql: think in sets of data, not rows of data.

By putting parentheses around our select statement, we created a set of data.

Then we queried from that set.

If you can properly define the sets of data that you need, the sql often becomes extremely simple.