April 11, 2012 at 3:30 pm
I'm working on a PIVOT, and I can't see to get it to work. Here is the PIVOT query:
SELECT *
FROM
(
SELECT
Listing.Person_ID
,Person.Person_Gender
FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_ID
WHERE Listing.Program_Version_Code_Listing = 'AR-2016'
AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept')
GROUP BY Listing.Person_ID
,Person.Person_Gender
) AS s
PIVOT
(
COUNT(s.Person_ID) FOR Person_Gender IN (Male, Female, [Not Specified])
) as p
Now I need to somehow get this query inside the above query. Obviously I written it as a standalone SELECT statement here, so I know it will need to be modified.
SELECT (AVG(DATEDIFF(DD,Birth_Date_Person,GETDATE()))/365.25) AS 'Avg Age'
FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_ID
WHERE Listing.Program_Version_Code_Listing = 'AR-2016'
AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept')
The final results will need to be something like this:
Male Female Not Specified Avg Age
100 89 0 19.8541
I've been wrestling with this all day. Does anyone have any thoughts?
April 11, 2012 at 3:34 pm
WHy dont u add the AVG statment in your inner query and use it as one of the columns in PIVOT Statement?
April 11, 2012 at 10:24 pm
April 12, 2012 at 8:46 am
Why dont u add the AVG statment in your inner query and use it as one of the columns in PIVOT Statement?
That is what I want to do, I'm just not sure how. When I add ,DATEDIFF(DD,Birth_Date_Student,GETDATE()/365.25) AS Test to the SELECT query, I get an "Implicit conversion from data type datetime to numeric is not allowed." So I'm just not sure how to get it in SELECT or PIVOT.
April 12, 2012 at 8:57 am
vinu512 (4/11/2012)
Please post the DDL of the tables and sample data so thatits easier for us to understand and provide a solution.Vinu Vijayan
Sure thing. Give me a little to create some scripts.
April 12, 2012 at 10:00 am
O.k., here is some test data for the scenario. Thanks again for all the help.
CREATE TABLE Listing
(
Person_IDCHAR(10)NOT NULL,
Program_Version_Code_ListingCHAR(10)NOT NULL,
Entity_Status_ListingVARCHAR(25)NOT NULL,
)
CREATE TABLE Person
(
Person_IDCHAR(10)NOT NULL,
Person_GenderCHAR(10)NOT NULL,
Birth_Date_StudentDATENOT NULL,
)
INSERT INTO Listing
(
Person_ID,
Program_Version_Code_Listing,
Entity_Status_Listing
)
VALUES
(234543, 'AR-2016', 'Accept'),
(846468, 'AR-2016', 'Conditional Accept'),
(897498, 'AR-2016', 'Accept')
INSERT INTO Person
(
Person_ID,
Person_Gender,
Birth_Date_Student
)
VALUES
('234543', 'Male', '1987-01-01'),
('897498', 'Female', '1974-08-09'),
('846468', 'Female', '1984-03-30')
April 12, 2012 at 11:04 am
THis?
SELECT Male = SUM (S.Male) ,Female = SUM(s.Female) ,Averg = MAX(s.Averg)
FROM
(
SELECT
Male = CASE WHEN Person_Gender = 'Male ' THEN 1 ELSE 0 END
,Female = CASE WHEN Person_Gender = 'Female ' THEN 1 ELSE 0 END
,Averg = AVG(DATEDIFF(DD,Birth_Date_Student,GETDATE()) / 365.25) OVER(PARTITION BY (SELECT NULL))
FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_ID
WHERE Listing.Program_Version_Code_Listing = 'AR-2016'
AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept')
) AS s
April 12, 2012 at 12:19 pm
ColdCoffee (4/12/2012)
THis?
SELECT Male = SUM (S.Male) ,Female = SUM(s.Female) ,Averg = MAX(s.Averg)FROM
(
SELECT
Male = CASE WHEN Person_Gender = 'Male ' THEN 1 ELSE 0 END
,Female = CASE WHEN Person_Gender = 'Female ' THEN 1 ELSE 0 END
,Averg = AVG(DATEDIFF(DD,Birth_Date_Student,GETDATE()) / 365.25) OVER(PARTITION BY (SELECT NULL))
FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_ID
WHERE Listing.Program_Version_Code_Listing = 'AR-2016'
AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept')
) AS s
That works great! Thanks so much. Is that an alternative to PIVOT? Also, one more question - I don't understand how the OVER(PARTITION BY(SELECT NULL)) is working. From articles I've read, PARTITION BY seems to split the results. But I haven't been able to wrap my head around it. If you could explain how that works, it would really help me understand. Thanks again. I learn so much from this place...
April 12, 2012 at 2:34 pm
A much better version:
SELECT
Male = SUM( CASE WHEN Person_Gender = 'Male ' THEN 1 ELSE 0 END )
,Female = SUM( CASE WHEN Person_Gender = 'Female ' THEN 1 ELSE 0 END)
,Averg = AVG(DATEDIFF(DD,Birth_Date_Student,GETDATE()) / 365.25)
FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_ID
WHERE Listing.Program_Version_Code_Listing = 'AR-2016'
AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept')
GROUP BY
CASE WHEN Listing.Person_ID = Listing.Person_ID THEN 1
ELSE 0
END
April 13, 2012 at 12:28 am
masonvann (4/12/2012)
Is that an alternative to PIVOT?
Yes! SUM(CASE...END) is an alternative to PIVOT. It is called as cross-tab . Read more about that in Jeff's article here http://www.sqlservercentral.com/articles/T-SQL/63681/
masonvann (4/12/2012)
I don't understand how the OVER(PARTITION BY(SELECT NULL)) is working. From articles I've read, PARTITION BY seems to split the results. But I haven't been able to wrap my head around it
PARTITION BY is like GROUP BY. When you specify a column in the PARTITION BY clause, it will take a set of DISTINCT rows in that column, perform the action (action is the function that we type before the OVER() clause, in our case AVG; you can use ROW_NUMBER or COUNT or SUM or any supported aggregate function before the OVER clause) specifed on the set of distinct values and display the result on each row. This is called as windowing! Create multiple windows and apply each output of the windows against each row!
Lets walk an example. Lets set-up data for our explanation!
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp;
CREATE TABLE #Temp
(
iD INT IDENTITY
,GroupNo INT
,AmountReceived INT
);
INSERT INTO #Temp (GroupNo, AmountReceived)
-- 3 rows in GroupNo 1 - Total amount is 30
SELECT 1, 10
UNION ALL SELECT 1, 10
UNION ALL SELECT 1, 10
-- 3 rows in GroupNo 2 - Total amount is 60
UNION ALL SELECT 2, 20
UNION ALL SELECT 2, 20
UNION ALL SELECT 2, 20
-- 2 rows in GroupNo 3 - Total amount is 100
UNION ALL SELECT 3, 50
UNION ALL SELECT 3, 50
-- 1 row in GroupNo 4 - Total amount is -10
UNION ALL SELECT 4, -10
;
Now look at the difference between the first and second query. The details of each query is given in the comments.
--Now if we need to get the total amount and
--the count of each group to be separate
--columns, but we still need all 9 rows from
--the table, we use window functions
-- If we GROUP BY, we will end up
-- aggregating 9 rows into 4 rows, based off
-- 4 disitnct GroupNo, which is not the request
-- Thus
SELECT T.iD
,T.GroupNo
,T.AmountReceived
-- This finds the SUM of AmountReceived of
-- distinct GroupNo and assign the result
-- to each respective Group
,SUM(T.AmountReceived) OVER (PARTITION BY T.GroupNo)
AS TotalAmountOfEachGroup
-- This finds the count of rows of each
-- distinct GroupNo and assign the result
-- to each respective Group
,COUNT(T.GroupNo) OVER (PARTITION BY T.GroupNo)
AS NumofRowsInEachGroup
-- Look at this here, i used SELECT NULL
-- I tell SQL Server to use a column that is
-- non-existent. SQL finds no columns/values
-- like that and does the COUNT for the entire
-- table and displays the result across all the
-- rows
,COUNT(T.GroupNo) OVER (PARTITION BY (SELECT NULL))
AS NumofRowsInTheTable
FROM #Temp T
;
-- Using GROUP BY
-- See here, we can not bring all the rows
-- We reduce the number of rows, as well as columns
SELECT T.GroupNo
,SUM(T.AmountReceived) AS TotalAmountOfEachGroup
,COUNT(T.GroupNo) AS NumofRowsInEachGroup
FROM #Temp T
GROUP BY T.GroupNo
;
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp;
Now, the PARITITION is based off a column. If i had used any of the columns from the table, then the AVG will be applied for each distinct value in that column, thus the result of AVG will not be 30.399, but rahter the AVG of every single row. To overcome this, i introduced a non-existent column (SELECT NULL), which just produce one row/column with NULL for each execution; as AVG is not applied over an existing column, it is applied across the whole table and the result is assinged to every single row of the table.
Hmmm.. Hope this clarifies you on what the query really does and what the PARTITION clause does. Let us know if you need more explanation..
Happy learning!
April 16, 2012 at 9:20 am
That is incredibly helpful! Thank you so much for the time and energy you put into the explanation. I know I'll be visiting this more than once!
April 16, 2012 at 10:12 am
masonvann (4/16/2012)
That is incredibly helpful! Thank you so much for the time and energy you put into the explanation. I know I'll be visiting this more than once!
Thanks for the feedback, mason. You're welcome.
April 11, 2013 at 7:27 am
It isn't a PIVOT ... you are just assembling single values into the fields of a single row:
with q0 as (
select p.Birth_Date_Student
, p.Person_Gender
from Listing l
join Person p
on l.[Person_ID] = p.[Person_ID]
where l.Program_Version_Code_Listing = 'AR-2016'
and l.Entity_Status_Listing in ('Accept', 'Conditional Accept')
) select [Male] = (select count(*) from q0 where Person_Gender = 'Male')
, [Female] = (select count(*) from q0 where Person_Gender = 'Female')
, [Unspecified] = (select count(*) from q0 where Person_Gender not in ('Male', 'Female'))
, [Age] = (select avg(datediff(day, Birth_Date_Student, sysdatetime()) / 365.25) from q0)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply