Using calculated columns and how to calc somethin

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

Using calculated columns and how to calc somethin

Author

Topic

mark007
Starting Member

3 Posts

Posted - 2011-07-26 : 13:25:52

1. I have the following SQL query:

SELECT dbo.CurrentResults.Status, CurrentResultsDetail_1.Tag, COUNT(dbo.CurrentResults.Identifier) AS 'Count', SUM(CurrentResultsDetail_1.TotalLiability)
AS 'Total Liability', SUM(CurrentResultsDetail_1.Pre88GMPLiability) AS 'Pre88 GMP Liability', SUM(CurrentResultsDetail_1.Post88GMPLiability)
AS 'Post88 GMP Liability', SUM(CurrentResultsDetail_1.TotalLiability * dbo.CurrentResults.Age) / SUM(CurrentResultsDetail_1.TotalLiability) AS LWAA,
SUM(CurrentResultsDetail_1.TotalLiability) /
(SELECT SUM(TotalLiability) AS Expr1
FROM dbo.CurrentResultsDetail) AS 'Proportion of liability', SUM(CurrentResultsDetail_1.Pre88GMPLiability) /
(SELECT SUM(TotalLiability) AS Expr1
FROM dbo.CurrentResultsDetail AS CurrentResultsDetail_3) AS 'Pre88GMP Proportion of liability', SUM(CurrentResultsDetail_1.Post88GMPLiability) /
(SELECT SUM(TotalLiability) AS Expr1
FROM dbo.CurrentResultsDetail AS CurrentResultsDetail_2) AS 'Post88GMP Proportion of liability', (SUM(CurrentResultsDetail_1.TotalLiability)
- SUM(CurrentResultsDetail_1.Pre88GMPLiability) - SUM(CurrentResultsDetail_1.Post88GMPLiability)) /
(SELECT SUM(TotalLiability) AS Expr1
FROM dbo.CurrentResultsDetail AS CurrentResultsDetail_2) AS 'XS Proportion of liability'
FROM dbo.CurrentResults INNER JOIN
dbo.CurrentResultsDetail AS CurrentResultsDetail_1 ON dbo.CurrentResults.ID = CurrentResultsDetail_1.MemberID
GROUP BY dbo.CurrentResults.Status, CurrentResultsDetail_1.Tag

For several of these columns they are dividing by a subquery:

(SELECT SUM(TotalLiability) AS Expr1
FROM dbo.CurrentResultsDetail AS CurrentResultsDetail_2)

How can I formulate the query so that this is only determined once and used for all columns. I'd hoped it was as simple as puttying an 'AS X' afetr it and dividing by X but this fails with invalid column.

2. I have data that looks like this:

Table1:

ID Name Age
e.g.
1 Mark 31

Table2:

ID MemberID Tag
1 1 Tag1
2 1 Tag1
3 1 Tag2

i.e. ID is unique, MemberId refers to ID in Table1 and Tag can be any value.

I would like to get the following:

Tag Count(MemberIDs) AverageAge
Tag1 1 31
Tag2 1 31

My initial reaction was:

Select Table2.Tag, Count(Table1.ID), Average(Table1.Age)
From Table1 inner join table2 on Table1.ID=Table2.MemberID
group by Table2.Tag

But this would return:

Tag Count(MemberIDs) AverageAge
Tag1 2 31
Tag2 1 31

i.e. tag 1 appears twice and similarly average age is wrong. What I want is the number of memberids where the tag appears at least once.

Any thoughts?

Thanks.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-26 : 16:57:02

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

For example, you aliased columns with single quotes and spaces or reserved words, vague names like “tag”, “id”, “status” and numeric suffixes all tell us that you are not an SQL programer. Those numeric post-fixes scream “stupid fake array in SQL!” and that the rest of DDL is going to be a mess.

>> How can I formulate the query so that this is only determined once and used for all columns. I'd hoped it was AS simple AS putting an 'AS X' after it and dividing by X but this fails with invalid column. <<

You really don't know how SELECT works in SQL. This is not COBOL or FORTRAN; it does not work left-to-right. Google some old postings on that topic.

Short, stupid answer: Put it in a VIEW or CTE. The application will still be crap, but it will get you over a hump. Now, if you actually want to do this right, then read the fist paragraphs of this posting and do that.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

mark007
Starting Member

3 Posts

Posted - 2011-07-26 : 18:25:38

WOW. I really must have misread the forum title. No wait it does indeed say "New to SQL Server Programming" so not that surprising therefore that I'm "not an SQL programer". I've seen some OTT replies on forums before but this is the best I've seen.

I'm a programmer rather than a DB/SQL programmer. Happy to take on board any constructive comments.

DDL=Data definition language? (guessing)
No idea what DRI is or what constraints you are referring to.
The thought od reading any ISO standard fills me with dread - let alone 2.

I've re-read my post and I'm pretty happy that I've provided pretty much everything you asked for - just not in SQL code. As a non DB guy working in visual studio rather than SQL management studio - generating the SQL for creating tables etc. is not something I do with any regularity.

Why is single quotes bad?
Using Count as a column name is not ideal but it works without trouble and suits my need. (assume this is your reserved word reference).
Tag, ID and Status are column names. They describe what the columns are perfectly to me. Not sure what makes them vague?
Numeric suffixes were auto added by visual studio - intention would be to tidy them up once happy it was working. (Other than Tag1, Tag2 etc. which is just sample data).
Not sure what else you think would be a mess. I have only 3 tables that are pretty simple. I showed you the layout of what I thought were the relevant fields for 2 of them that I need to get some summary data from.

"You really don't know how SELECT works in SQL. This is not COBOL or FORTRAN; it does not work left-to-right. Google some old postings on that topic."

Clearly....do you propose I google "I don't know how select works in SQL?". I spent quite a bit of time googling before posting. I've stumbled across CTE but based on what I saw couldn't seem to fit it in, CROSS APPLY looked promising too but I couldn't get it working (not supported...).

I would like to "get it right". Eager to learn more.

End result is I've come up with a stored procedure after spending more tome googling before seeing this.

This seems to achievve the first part of the problem well:


ALTER PROCEDURE dbo.StoredProcedure1
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
AS
	/* SET NOCOUNT ON */
	Declare @TL float
	
	select @TL=SUM([TotalLiability]) from CurrentResults 
	
	SELECT	Status, 
			Tag, 
			COUNT(Count) AS 'Count',
			SUM([Total Liability]) as 'Liability', 
			Sum([Pre88 Liability]) As 'Pre88Liability', 
			Sum([Post88 Liability]) as 'Post88Liability', 
			SUM([Total Liability] * Age) / SUM([Total Liability]) AS LWAA, 
			SUM([Total Liability])/@TL AS 'Proportion of liability', 
            SUM([Pre88 Liability])/@TL AS 'Pre88GMP Proportion of liability', 
            SUM([Post88 Liability])/@TL AS 'Post88GMP Proportion of liability', 
            SUM(([Total Liability] - [Pre88 Liability] - [Post88 Liability]))/@TL AS 'XS Proportion of liability'
FROM        dbo.ValuesByStatusTag
GROUP BY	Status, Tag
	
	UNION
	
	select	Status,
			'Total', 
			Count(ID), 
			SUM([TotalLiability]),
			SUM([Pre88GMPLiability]),
			SUM([Post88GMPLiability]),
			SUM([TotalLiability]*Age)/sum(TotalLiability),
			1,
			SUM([Pre88GMPLiability])/@TL,
			SUM([Post88GMPLiability])/@TL,
			SUM([TotalLiability]-[Pre88GMPLiability]-[Post88GMPLiability])/@TL
	from CurrentResults
	Group By Status
	
	
	
	RETURN

I also have a View that gets past my first issue which is queried in the above:


SELECT        ID, Status, Tag, COUNT(Identifier) AS Count, SUM(Age) / COUNT(Age) AS Age, CASE WHEN SUM(sex) > 0 THEN 'F' ELSE 'M' END AS Sex, SUM(SubTotalLiab) 
                         AS [Total Liability], SUM(SubTotalPre88) AS [Pre88 Liability], SUM(SubTotalPost88) AS [Post88 Liability], SUM(PensionCDAT) AS PensionCDAT
FROM            dbo.ResultsDetail
GROUP BY ID, Status, Tag

This does a first layer of grouping that means I can then group by tag again to get the figures I need. ResultsDetail is another view that joins the 2 tables to make the SQL less cumbersome.

If you have any comments on this then please do share. This is the absolute first stored procedure I've ever created.

End result is:


Status	Tag	Count	Liability	Pre88Liability	Post88Liability	LWAA	Prop. of liab	Pre88GMP Prop	Post88GMP Prop	        XS Prop
D	Post97	50	1508108.071	0	        0	        53.00249017	0.40480913	0	        0	        0.40480913
D	Pre97	44	2217371.31	719679.7304	1275897.911	57.00971396	0.595190868	0.193177751	0.342478854	0.059534263
D	Total	50	3725479.386	719679.7304	1275897.916	55.38755321	1	        0.193177751	0.342478855	0.464343393

Any suggestions/comments/discussion/advice appreciated. If you could tone down the jargon and "talking down" that would also be appreciated.

Thanks. I do appreciate the time spent by people on forums such as these - I'm used to being one of the founders or experts answering such queries in my world of programming (which isn't SQL as you're well aware!!!). Interesting being on the other end....

:-)

mark007
Starting Member

3 Posts

Posted - 2011-07-28 : 07:33:47

There were a couple of issues with the approach I was using once I added an extra status category. Not to be put off I played around abut more and stumbled across over() which seemed pretty helpful.

My final query is in case it helps someone in the future is:


SELECT status, 
       tag, 
       COUNT(status)                                           AS COUNT, 
       AVG(age)                                                AS aveage, 
       AVG(pensioncdat)                                        AS avepension, 
       SUM(subtotalliab)                                       AS liab, 
       SUM(subtotalpre88)                                      AS pre88liab, 
       SUM(subtotalpost88)                                     AS post88liab, 
       SUM(subtotalliab * age) / SUM(subtotalliab)             AS lwaa, 
       SUM(subtotalliab) / tl                                  AS prop, 
       SUM(subtotalpre88) / tl                                 AS pre88, 
       SUM(subtotalpost88) / tl                                AS post88, 
       SUM(subtotalliab - subtotalpre88 - subtotalpost88) / tl AS xs 
FROM   (SELECT status, 
               tag, 
               age, 
               pensioncdat, 
               subtotalliab, 
               subtotalpre88, 
               subtotalpost88, 
               CAST(SUM(subtotalliab) OVER (PARTITION BY status) AS FLOAT) AS tl 
        FROM   (SELECT id, 
                       status, 
                       tag, 
                       COUNT(identifier)     AS COUNT, 
                       SUM(age) / COUNT(age) AS age, 
                       CASE 
                         WHEN SUM(sex) > 0 THEN 'F' 
                         ELSE 'M' 
                       END                   AS sex, 
                       SUM(subtotalliab)     AS subtotalliab, 
                       SUM(subtotalpre88)    AS subtotalpre88, 
                       SUM(subtotalpost88)   AS subtotalpost88, 
                       SUM(pensioncdat)      AS pensioncdat 
                FROM   resultsdetail 
                GROUP  BY id, 
                          status, 
                          tag) AS tb2) AS tb1 
GROUP  BY status, 
          tag, 
          tl 
UNION 
SELECT status, 
       'Total', 
       COUNT(status)                                           AS COUNT, 
       AVG(age)                                                AS aveage, 
       AVG(pensioncdat)                                        AS avepension, 
       SUM(subtotalliab)                                       AS liab, 
       SUM(subtotalpre88)                                      AS pre88liab, 
       SUM(subtotalpost88)                                     AS post88liab, 
       SUM(subtotalliab * age) / SUM(subtotalliab)             AS lwaa, 
       SUM(subtotalliab) / tl                                  AS prop, 
       SUM(subtotalpre88) / tl                                 AS pre88, 
       SUM(subtotalpost88) / tl                                AS post88, 
       SUM(subtotalliab - subtotalpre88 - subtotalpost88) / tl AS xs 
FROM   (SELECT status, 
               age, 
               pensioncdat, 
               subtotalliab, 
               subtotalpre88, 
               subtotalpost88, 
               CAST(SUM(subtotalliab) OVER (PARTITION BY status) AS FLOAT) AS tl 
        FROM   (SELECT id, 
                       status, 
                       COUNT(identifier)     AS COUNT, 
                       SUM(age) / COUNT(age) AS age, 
                       CASE 
                         WHEN SUM(sex) > 0 THEN 'F' 
                         ELSE 'M' 
                       END                   AS sex, 
                       SUM(subtotalliab)     AS subtotalliab, 
                       SUM(subtotalpre88)    AS subtotalpre88, 
                       SUM(subtotalpost88)   AS subtotalpost88, 
                       SUM(pensioncdat)      AS pensioncdat 
                FROM   resultsdetail 
                GROUP  BY id, 
                          status) AS tb2) AS tb1 
GROUP  BY status, tl

This does the task quite nicely. You'll note that it is just nested queries to one original table (view actually - just joins 2 tables).

Output is:


D	Post97	50	50.367422790527343	1973.1552	1508108.0708	0.0000	        0.0000	        53.0024..	0.4048..	0	0     	0.4048..
D	Pre97	44	50.727894869717687	3926.7574	2217371.3096	359839.8652	637948.9552	57.0097..	0.5951..	0.0965..	0.1712..	0.3273..
D	Total	50	50.367422790527343	5428.7018	3725479.3804	359839.8652	637948.9552	55.3875..	1               0.0965..	0.1712..	0.7321..
P	AVC/DC	4	71.202629089355469	2261.5651	108505.5644	0.0000	        0.0000	        73.1092..	0.0676..	0	0     	0.0676..
P	Post97	17	66.9481465956744	1065.4368	312855.9918	0.0000   	0.0000	        67.1163..	0.1951..	0	0     	0.19512..
P	Pre97	17	67.467566546271826	5137.8677	1181973.8889	305152.0158	466691.3152	68.6592..	0.7371..	0.1903..	0.2910..	0.2557..
P	Total	18	66.718759536743164	6361.2466	1603335.4451	305152.0158	466691.3152	68.6592..	1      	        0.19032..	0.2910..	0.51860..

As always suggestions appreciated.

:-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources