Solved

t-sql question - multiple select statements

Posted on 2007-11-28
9
1,207 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 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