Solved

t-sql question - multiple select statements

Posted on 2007-11-28
9
1,219 Views
Last Modified: 2012-06-27
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












0
Comment
Question by:mugsey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 400 total points
ID: 20364678
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
 
LVL 23

Assisted Solution

by:Ashish Patel
Ashish Patel earned 100 total points
ID: 20364691
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
 
LVL 25

Expert Comment

by:jogos
ID: 20364741
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:mugsey
ID: 20364783
Thanks guys

The fields will either be zero or 1
0
 

Author Comment

by:mugsey
ID: 20364803
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20364821
So, did the code for select statment give you the results?
0
 

Author Comment

by:mugsey
ID: 20364842
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
 

Author Comment

by:mugsey
ID: 20364846
Sorry

asvforce:

I would need the query to accept either zero 1 or NULL.  
0
 
LVL 25

Expert Comment

by:jogos
ID: 20364926
<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

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question