t-sql question - multiple select statements

I have two tables

tblUser

UserID      guid
isOver18      int


tblUserdetails

UserID            guid
isInUSA            int
isReceiveInfo      int

Now I want to do a select statement that does the following
I need the result of the select statement to say somethink like the following
            
Number of users                        :      135
Number of over 18's                  :      87
Number of users in USA                  :      54
Number who want to receive info            :      47












mugseyAsked:
Who is Participating?
 
jogosConnect With a Mentor Commented:
select count(tblUser.UserId) as nbrUsers
      ,sum(isnull(isOver18,0)) as nbrOver18      
      ,sum(isnull(isInUSA,0)) as nbrInUSA    
from tbluser
left join tblUserdetails on tblUserdetails.UserId = tblUser.UserId
0
 
Ashish PatelConnect With a Mentor Commented:
Try this
Select * From (select 'Number of users:' Descrip, Count(A.UserID) Value From tblUser a, tblUserDetails b where a.UserID = b.UserID
Union
select 'Number of over 18''s:' Descrip, Count(IsOver18) Value From tblUser a, tblUserDetails b where a.UserID = b.UserID
Union
select 'Number of users in USA:' Descrip, Count(IsInUSA) Value From tblUser a, tblUserDetails b where a.UserID = b.UserID
Union
select 'Number who want to receive info:' Descrip, Count(isReceiveInfo) Value From tblUser a, tblUserDetails b where a.UserID = b.UserID ) xyz

Open in new window

0
 
jogosCommented:
asyforce-solution goes for the layout, mine was focused on the counting

Differences
1)  mine  :   ,sum(isnull(isOver18,0)) as nbrOver18      
When isOver18 = 1 when true then sum works fine, must be 0 or null for false
2) astforce:  count(IsOver18)
Counts the times column IsOver18 is filled up, so must be NULL when false
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mugseyAuthor Commented:
Thanks guys

The fields will either be zero or 1
0
 
mugseyAuthor Commented:
0
 
Ashish PatelCommented:
So, did the code for select statment give you the results?
0
 
mugseyAuthor Commented:
OK Guys

I get

The count aggregate operation cannot take a uniqueidentifier data type as an argument

if I try to do a count as I don't think you can do it on GUIDs?
0
 
mugseyAuthor Commented:
Sorry

asvforce:

I would need the query to accept either zero 1 or NULL.  
0
 
jogosCommented:
<The count aggregate operation cannot take a uniqueidentifier data type as an argument >
Count(*) in case of count(UserID) will do the trik.

<asvforce:
I would need the query to accept either zero 1 or NULL.  >
I've already counterd that question, see differences.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.