Solved

t-sql question - multiple select statements

Posted on 2007-11-28
9
1,224 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mugsey
ID: 20364783
Thanks guys

The fields will either be zero or 1
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

617 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