February 6, 2013 at 1:09 pm
Hello. I am having trouble getting the correct query for this. What I want to do is:
QueryA
Group Type Amount
Dog Typea 1
Dog Typeb 2
Dog Typec 3
Cat Typea 10
Cat Typeb 20
QueryB
Group TotalAmount
Dog 6
Cat 30
Nested Query Would Show: (This is what I am trying to get the correct syntax for)
Group Type % of Total
Dog Typea 0.166
Dog Typeb 0.333
Dog Typec 0.5
Cat Typea 0.33
Cat Typeb 0.66
Thank you!!
February 6, 2013 at 1:21 pm
Something like this should work. Notice how I posted sample data in a readily consumable format. That is something you should do in the future.
with DogCat(Animal, Amount) as
(
select 'Dog', 1 union all
select 'Dog', 2 union all
select 'Dog', 3 union all
select 'Cat', 10 union all
select 'Cat', 20
)
select Animal, Amount * 1.0 / Amt as '% of Total'
from
(
select Animal, Amount, sum(Amount) over(partition by Animal) as Amt
from DogCat
) x
order by Animal desc, Amount * 1.0 / Amt
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 5:27 am
Thanks. I'm just confused about this part:
with DogCat(Animal, Amount) as
(
select 'Dog', 1 union all
select 'Dog', 2 union all
select 'Dog', 3 union all
select 'Cat', 10 union all
select 'Cat', 20
)
I don't need to setup this information? I already have it included in a table. Plus there was one additional category field included. Sorry, will try some of what you listed.
Thank you!
February 7, 2013 at 7:41 am
mjbkm (2/7/2013)
Thanks. I'm just confused about this part:with DogCat(Animal, Amount) as
(
select 'Dog', 1 union all
select 'Dog', 2 union all
select 'Dog', 3 union all
select 'Cat', 10 union all
select 'Cat', 20
)
I don't need to setup this information? I already have it included in a table. Plus there was one additional category field included. Sorry, will try some of what you listed.
Thank you!
I had to do that because I don't have your table. The point I was making is that you should be posting something like that so that the people who are helping you can start on the problem instead of setting it up. Since I had to type in the data myself I left out the type because it really isn't part of the issue here.
Here I included your column. BTW, you really should avoid reserved words as column names, they make things more difficult to work with.
with DogCat(Animal, [Type], Amount) as
(
select 'Dog', 'Typea', 1 union all
select 'Dog', 'Typeb', 2 union all
select 'Dog', 'Typec', 3 union all
select 'Cat', 'Typea', 10 union all
select 'Cat', 'Typeb', 20
)
select Animal, [Type], Amount * 1.0 / Amt as '% of Total'
from
(
select Animal, [Type], Amount, sum(Amount) over(partition by Animal) as Amt
from DogCat
) x
order by Animal desc, Amount * 1.0 / Amt
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 8:08 am
;WITH SampleData ([Group], [Type], Amount) AS (
SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typeb', 20
)
SELECT
d.[Group],
d.[Type],
d.Amount,
x.Percentage
FROM (
SELECT
[Group],
[Type],
Amount,
GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])
FROM SampleData
) d
CROSS APPLY (SELECT Percentage = CAST(d.Amount*1.00/d.GroupTotal AS NUMERIC(5,2))) x
ORDER BY d.[Group] DESC, x.Percentage
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 8:10 am
OK... I am getting. (sorry) Very helpful. One more question. If I change your code to make there by multiple types by animal first. I would then need to sum the values of animal and type first. Then do the % of total.
with DogCat(Animal, WhatType, Amount) as
(
select 'Dog', 'Typea', 1 union all
select 'Dog', 'Typeb', 2 union all
select 'Dog', 'Typec', 3 union all
select 'Cat', 'Typea', 10 union all
select 'Cat', 'Typeb', 20 union all
select 'Cat', 'Typeb', 15 union all
select 'Cat', 'Typeb', 8
)
select Animal, WhatType, Amount * 1.0 / Amt as '% of Total'
from
(
select Animal, WhatType, Amount, sum(Amount) over(partition by Animal) as Amt
from DogCat
) x
order by Animal desc, Amount * 1.0 / Amt
My results would give me multiple typeb for Cats. How do I sum them first so I only have on type per animal?
February 7, 2013 at 8:11 am
Also how did you get the code highlighted above?
February 7, 2013 at 8:14 am
ChrisM@Work (2/7/2013)
;WITH SampleData ([Group], [Type], Amount) AS (SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typeb', 20
)
SELECT
d.[Group],
d.[Type],
d.Amount,
x.Percentage
FROM (
SELECT
[Group],
[Type],
Amount,
GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])
FROM SampleData
) d
CROSS APPLY (SELECT Percentage = CAST(d.Amount*1.00/d.GroupTotal AS NUMERIC(5,2))) x
ORDER BY d.[Group] DESC, x.Percentage
Nice Chris. That will certainly outperform mine since it only has to do the calculation one time. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 8:17 am
This is working great too. What if I add multiple types per animal in first. How do I group and total first and then get the same unique results by group/type. Only one line for each group/type.
SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typea', 80 UNION ALL
SELECT 'Cat', 'Typea', 30 UNION ALL
SELECT 'Cat', 'Typea', 20 UNION ALL
SELECT 'Cat', 'Typeb', 20
February 7, 2013 at 8:22 am
Sean Lange (2/7/2013)
ChrisM@Work (2/7/2013)
;WITH SampleData ([Group], [Type], Amount) AS (SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typeb', 20
)
SELECT
d.[Group],
d.[Type],
d.Amount,
x.Percentage
FROM (
SELECT
[Group],
[Type],
Amount,
GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])
FROM SampleData
) d
CROSS APPLY (SELECT Percentage = CAST(d.Amount*1.00/d.GroupTotal AS NUMERIC(5,2))) x
ORDER BY d.[Group] DESC, x.Percentage
Nice Chris. That will certainly outperform mine since it only has to do the calculation one time. 😀
Possibly 😉
Actually I misread your post Sean - thought you had used the "aggregate in a derived table" trick, or I wouldn't have posted this!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 8:23 am
mjbkm (2/7/2013)
This is working great too. What if I add multiple types per animal in first. How do I group and total first and then get the same unique results by group/type. Only one line for each group/type.SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typea', 80 UNION ALL
SELECT 'Cat', 'Typea', 30 UNION ALL
SELECT 'Cat', 'Typea', 20 UNION ALL
SELECT 'Cat', 'Typeb', 20
Can you post your expected results please? It removes any ambiguity in your explanation, making it much easier to code.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 8:28 am
These all are working great except that I forgot the first step... I need to SUM the values first. So if I have multiple group / types... I don't get a unique amount / percentage for each Group/type. I need to group and sum them first. Is this possible? Since I have millions of Group/Type detail records.
Thank you so much!!! Huge help!!
WITH SampleData ([Group], [Type], Amount) AS (
SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typeb', 20
)
February 7, 2013 at 8:37 am
WITH SampleData ([Group], [Type], Amount) AS (
SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typeb', 20
)
SELECT
d.[Group],
d.[Type],
d.Amount,
x.Percentage
FROM (
SELECT
[Group],
[Type],
Amount,
GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])
FROM SampleData
) d
CROSS APPLY (SELECT Percentage = CAST(d.Amount*1.00/d.GroupTotal AS NUMERIC(5,2))) x
ORDER BY d.[Group] DESC, x.Percentage
Gives me:
GroupTypeAmountPercentage
DogTypea10.17
DogTypeb20.33
DogTypec30.50
CatTypea100.17
CatTypea100.17
CatTypea100.17
CatTypea100.17
CatTypeb200.33
I need it to be:
DogTypea116.67%
DogTypeb233.33%
DogTypec350.00%
CatTypea4066.67%
CatTypeb2033.33%
February 7, 2013 at 8:41 am
Aggregate Amount by Group and type, then feed the result into Sean's original query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 8:47 am
ChrisM@Work (2/7/2013)
Aggregate Amount by Group and type, then feed the result into Sean's original query.
That is where I was stuck before. How would I change this?
SELECT
[Group],
[Type],
Amount,
GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])
FROM SampleData
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply