[RESOLVED] Question about query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

[RESOLVED] Question about query?

Author

Topic

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-29 : 14:07:56

Okay, so I wrote this query the other day, and it works great but im wondering if this is the correct / best way to write this query.

Im new to databasing and whatnot and need some guidance.

I like this query because it returns all results in 1 row.

Is there a better, nicer, cleaner way to do this??

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_ViewClientStats]
AS
BEGIN
SET NOCOUNT ON;

SELECT

TotalAccounts = (SELECT Count(UserName) FROM csUsers),

Activated = (SELECT Count(UserName) FROM csUsers WHERE IsApproved = 1),
UnActivated = (SELECT Count(UserName) FROM csUsers WHERE IsApproved = 0),

ClientAccounts = (SELECT Count(UserName) FROM csUsers WHERE AccountType = 'C'),
DemoAccounts = (SELECT Count(UserName) FROM csUsers WHERE AccountType = 'D'),
PromoAccounts = (SELECT Count(UserName) FROM csUsers WHERE AccountType = 'P'),

TotalProfiles = (SELECT Count(ProfileName) FROM csProfiles),

HomePhones = (SELECT Count(Number) FROM csPhoneNumbers WHERE Lower(PhoneType) = 'home' AND Is911Phone = 1),
CellPhones = (SELECT Count(Number) FROM csPhoneNumbers WHERE Lower(PhoneType) = 'cell' AND Is911Phone = 1),

TotalAllowedProfiles = (SELECT SUM (ProfileLimit) FROM csUsers),

TotalDemoProfileLimit = (SELECT SUM(ProfileLimit) FROM csUsers WHERE AccountType = 'D'),
TotalPromoProfileLimit = (SELECT SUM(ProfileLimit) FROM csUsers WHERE AccountType = 'P'),
TotalClientProfileLimit = (SELECT SUM(ProfileLimit) FROM csUsers WHERE AccountType = 'C'),

MonthlyIncome = '$'+CONVERT(VARCHAR, CONVERT(Money, (SELECT SUM (ProfileLimit * 9.99) FROM csUsers WHERE AccountType = 'C')) ,1),
YearlyIncome = '$'+CONVERT(VARCHAR, CONVERT(Money, (SELECT (SUM(ProfileLimit * 9.99) * 12) FROM csUsers WHERE AccountType = 'C')) ,1)

END

Sucess comes before work only in the dictionary.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-29 : 14:38:28

How do users and csPhoneNumbers link together?

Jim

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-29 : 14:56:02

A User can have a Residence which can inturn have a House Phone.
And a User can also have multiple profiles which can have Cell Phones.

I have a few cross ref tables, but i just wanted to get the totals.

Sucess comes before work only in the dictionary.

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-29 : 14:57:05

Should i take the phone numbers out?

Sucess comes before work only in the dictionary.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-29 : 15:05:26

Okay, still not sure how to link in csPhineNumbers, but this a better way for the csUsers data

SELECT [TotalAccounts] = count(username)
,[Activated] = sum(case where IsApproved = 1 then 1 else 0 end)
,[UnActivated] = sum(case where IsApproved = 0 then 1 else 0 end)
,[ClientAccounts] = sum(case when AccountType = 'C' then 1 else 0 end)
,...
,[TotalDemoProfileLimit] = SUM(case when AccountType = 'D' then ProfileLimit else 0 end)
,etc.
FROM
csUsers

Jim

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-29 : 15:08:27

Thanks Jim!

Very nice.. I did not know you could do it that way..

Can you explain to me about the case statements? Why are those there?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 15:18:36

[code]ALTER PROCEDURE dbo.usp_ViewClientStats
AS

SET NOCOUNT ON

SELECT COUNT(*) AS TotalAccounts,
SUM(CASE WHEN IsApproved = 1 THEN 1 ELSE 0 END) AS Activated,
SUM(CASE WHEN IsApproved = 0 THEN 1 ELSE 0 END) AS UnActivated,
SUM(CASE WHEN AccountType = 'C' THEN 1 ELSE 0 END) AS ClientAccounts ,
SUM(CASE WHEN AccountType = 'D' THEN 1 ELSE 0 END) AS DemoAccounts ,
SUM(CASE WHEN AccountType = 'P' THEN 1 ELSE 0 END) AS PromoAccounts,
SUM(ProfileCount) AS TotalProfiles,
MAX(csProfiles.ProfileLimit) AS TotalAllowedProfiles,
MAX(csPhoneNumbers.HomePhones) AS HomePhones,
MAX(csPhoneNumbers.CellPhones) AS CellPhones,
SUM(CASE WHEN AccountType = 'D' THEN ProfileLimit ELSE 0 END) AS TotalDemoProfileLimit,
SUM(CASE WHEN AccountType = 'P' THEN ProfileLimit ELSE 0 END) AS TotalPromoProfileLimit,
SUM(CASE WHEN AccountType = 'C' THEN ProfileLimit ELSE 0 END) AS TotalClientProfileLimit,
'$' + CONVERT(VARCHAR(20), CONVERT(MONEY, SUM(CASE WHEN AccountType = 'C' THEN ProfileLimit * 9.99 ELSE 0 END), 1)) AS MonthlyIncome,
'$' + CONVERT(VARCHAR(20), CONVERT(MONEY, SUM(CASE WHEN AccountType = 'C' THEN ProfileLimit * 9.99 * 12 ELSE 0 END), 1)) AS YearlyIncome
FROM csUsers
CROSS JOIN (
SELECT COUNT(*) AS ProfileCount
FROM csProfiles
) AS csProfiles
CROSS JOIN (
SELECT SUM(CASE WHEN PhoneType = 'Home' THEN 1 ELSE 0 END) AS HomePhones,
SUM(CASE WHEN PhoneType = 'cell' THEN 1 ELSE 0 END) AS CellPhones
FROM csPhoneNumbers
WHERE Is911Phone = 1
AND PhoneType IN ('Home', 'Cell')
) AS csPhoneNumbers[/code]

N 56°04'39.26"
E 12°55'05.63"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-29 : 15:23:41

look up CASE statements in BOoks On line, but there like "If" statements.
this statement is like saying "if IsApproved = 1 then count it"

sum(case where IsApproved = 1 then 1 else 0 end)

Jim

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-29 : 15:28:20

Thanks Jim, i know about case statements. I just wasnt understanding the query.. I got it now.. It works very nice.

Peso! Absolutely Genius!

Thats amazing.. I wish I could write queries like that!

Sucess comes before work only in the dictionary.

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-29 : 15:34:26

Hi Peso,

I am getting an error with the query.

Its saying 'Invalid Column Name 'ProfileName' on line 8.

I know the column is there.

SELECT COUNT(*) AS TotalAccounts,
SUM(CASE WHEN IsApproved = 1 THEN 1 ELSE 0 END) AS Activated,
SUM(CASE WHEN IsApproved = 0 THEN 1 ELSE 0 END) AS UnActivated,
SUM(CASE WHEN AccountType = 'C' THEN 1 ELSE 0 END) AS ClientAccounts ,
SUM(CASE WHEN AccountType = 'D' THEN 1 ELSE 0 END) AS DemoAccounts ,
SUM(CASE WHEN AccountType = 'P' THEN 1 ELSE 0 END) AS PromoAccounts,
SUM(ProfileLimit) AS TotalAllowedProfiles,
MAX(csProfiles.ProfileName) AS TotalProfiles,
MAX(csPhoneNumbers.HomePhones) AS HomePhones,
MAX(csPhoneNumbers.CellPhones) AS CellPhones,
SUM(CASE WHEN AccountType = 'D' THEN ProfileLimit ELSE 0 END) AS TotalDemoProfileLimit,
SUM(CASE WHEN AccountType = 'P' THEN ProfileLimit ELSE 0 END) AS TotalPromoProfileLimit,
SUM(CASE WHEN AccountType = 'C' THEN ProfileLimit ELSE 0 END) AS TotalClientProfileLimit,
'$' + CONVERT(VARCHAR(20), CONVERT(MONEY, SUM(CASE WHEN AccountType = 'C' THEN ProfileLimit * 9.99 ELSE 0 END), 1)) AS MonthlyIncome,
'$' + CONVERT(VARCHAR(20), CONVERT(MONEY, SUM(CASE WHEN AccountType = 'C' THEN ProfileLimit * 9.99 * 12 ELSE 0 END), 1)) AS YearlyIncome
FROM csUsers
CROSS JOIN (
SELECT COUNT(*) AS ProfileCount
FROM csProfiles
) AS csProfiles
CROSS JOIN (
SELECT SUM(CASE WHEN PhoneType = 'Home' THEN 1 ELSE 0 END) AS HomePhones,
SUM(CASE WHEN PhoneType = 'Cell' THEN 1 ELSE 0 END) AS CellPhones
FROM csPhoneNumbers
WHERE Is911Phone = 1
AND PhoneType IN ('Home', 'Cell')
) AS csPhoneNumbers

Also, what does the MAX function do?

Sucess comes before work only in the dictionary.

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-29 : 18:02:38

Okay i fixed that but now its giving me null values instead of zeros.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_ViewClientStats]
AS
BEGIN
SET NOCOUNT ON;

SELECT [TotalAccounts] = count(*),
[Activated] = sum(case when IsApproved = 1 then 1 else 0 end),
[UnActivated] = sum(case when IsApproved = 0 then 1 else 0 end),

[ClientAccounts] = sum(case when AccountType = 'C' then 1 else 0 end),
[DemoAccounts] = sum(case when AccountType = 'D' then 1 else 0 end),
[PromoAccounts] = sum(case when AccountType = 'P' then 1 else 0 end),

[TotalAllowedProfiles] = sum(ProfileLimit),
[TotalProfiles] = max(csProfiles.ProfileCount), --returns null when 0

[TotalClientProfileLimit] = sum(case when AccountType = 'C' then ProfileLimit else 0 end),
[TotalDemoProfileLimit] = sum(case when AccountType = 'D' then ProfileLimit else 0 end),
[TotalPromoProfileLimit] = sum(case when AccountType = 'P' then ProfileLimit else 0 end),

[CellPhones] = max(csPhoneNumbers.CellPhones), --returns null when 0
[HomePhones] = max(csPhoneNumbers.HomePhones), --returns null when 0

[MonthlyIncome] = '$' + CONVERT(VARCHAR, CONVERT(MONEY, SUM(CASE WHEN AccountType = 'C' THEN ProfileLimit * 9.99 ELSE 0 END), 1), 1),
[YearlyIncome] = '$' + CONVERT(VARCHAR, CONVERT(MONEY, SUM(CASE WHEN AccountType = 'C' THEN ProfileLimit * 9.99 * 12 ELSE 0 END), 1), 1)

FROM csUsers

CROSS JOIN (
SELECT COUNT(*) AS ProfileCount
FROM csProfiles
) AS csProfiles

CROSS JOIN (
SELECT SUM(CASE WHEN PhoneType = 'Home' THEN 1 ELSE 0 END) AS HomePhones,
SUM(CASE WHEN PhoneType = 'Cell' THEN 1 ELSE 0 END) AS CellPhones
FROM csPhoneNumbers
WHERE Is911Phone = 1
AND PhoneType IN ('Home', 'Cell')
) AS csPhoneNumbers

END

Sucess comes before work only in the dictionary.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 18:27:09

If you run the derived tables separately, which results do you get?

N 56°04'39.26"
E 12°55'05.63"

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-29 : 19:20:19

I got it working now.

How do I resolve my post on this forum? There is no resolved button

Sucess comes before work only in the dictionary.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 01:05:24

You can edit the top post in this topic and change the title to "[RESOLVED] + {Title name before}"
What did you change to make it work?

N 56°04'39.26"
E 12°55'05.63"

coldfiretech
Starting Member

30 Posts

Posted - 2009-06-30 : 15:24:22

It was my mistake. The 'Cell' and 'Home' values should have been 'Cellular' and 'House'

Thank you very much for your help! I learned quite a bit from your query. I still need to learn more about SUM, MAX, ect..

:)

Sucess comes before work only in the dictionary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources