Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

t-sql question - multiple select statements

Posted on 2007-11-28
9
Medium Priority
?
1,226 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 1600 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 400 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 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