November 25, 2011 at 3:24 am
Hi Guys,
I have managed to keep off here for a few weeks and have doing well, I am having a problem with a calculation using a derived table, and annoyingly the derived part works fine.
In this code I am working out 2 numbers a 'Total' and then a 'Number' and then working out the % of the total.
In my derived table I am working out a number of rows that meat a criteria, I then need to divide this by the total number of rows for that ID in this case 'ACCGRPNUM' where can I place the COUNT (ACCGRPNUM) to use it in the first select statement, do I have to calculate this in another derived table?! At present I receive the error saying I can not calculate the total within an aggregate function, so to clarify where do I put the calculation that works out the total?!
Any hints tips pointers would be great :current code below
USE DQ_RefData
-- Calculate %--
Select
ACCGRPNUM,
ACCGRPNAME,
REGION,
SUM (a.Pass/(COUNT (ACCGRPNUM))) as RATE
-- LOCATIONS THAT MEET CRITERIA--
From (
Select
ACCGRPNUM,
ACCGRPNAME,
REGION,
COUNT (LOCID) AS Pass
From Cargo_Mapping
Where GeoResolutionCode <= '5'
Group By ACCGRPNUM, ACCGRPNAME, REGION
)a
Order By ACCGRPNAME
Thanks Guys 😎
November 25, 2011 at 3:39 am
Off the bat it looks like you're missing a GROUP BY (formatted you code below)
USE DQ_RefData
-- Calculate %--
SELECT ACCGRPNUM, ACCGRPNAME, REGION,
SUM(a.Pass / (COUNT(ACCGRPNUM))) AS RATE
-- LOCATIONS THAT MEET CRITERIA--
FROM (SELECT ACCGRPNUM, ACCGRPNAME, REGION,
COUNT(LOCID) AS Pass
FROM Cargo_Mapping
WHERE GeoResolutionCode <= '5'
GROUP BY ACCGRPNUM, ACCGRPNAME, REGION) a
--MISSING A GROUP BY HERE!! <ACCGRPNUM, ACCGRPNAME, REGION>
ORDER BY ACCGRPNAME
If that doesn't work, please read this article[/url] and provide us with DDL and readily consumable sample data.
November 25, 2011 at 3:49 am
That didnt do it...damn it!
The error I am guessing is here
SUM (a.Pass/(COUNT (ACCGRPNUM))) as RATE
using the ACCGRPNUM to get the total number of locations by Region kicks out this error:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Which makes me think I need to work out the total somewhere else in the query?!
I will get some sample data together....
Thanks again
November 25, 2011 at 4:16 am
methexis (11/25/2011)
That didnt do it...damn it!The error I am guessing is here
SUM (a.Pass/(COUNT (ACCGRPNUM))) as RATE
using the ACCGRPNUM to get the total number of locations by Region kicks out this error:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Which makes me think I need to work out the total somewhere else in the query?!
I will get some sample data together....
Thanks again
My apologies, I didn't read your code properly. You're correct, the issue is because you can't do a SUM(someData / COUNT(someMoreData) ).
When you post back with DDL and sample data, it's fairly trivial to fix.
November 25, 2011 at 5:05 am
Okay I hope I followed the rules on this!
Create Table Derived_Help
(
ACCGRPNUM varchar(20),
ACCGRPNAME varchar(40),
Region varchar(15),
GeoResolutionCode smallint
)
Insert Into Derived_Help
(ACCGRPNUM,ACCGRPNAME,Region, GeoResolutionCode)
Select '1234','CompanyX', 'US', '5' UNION ALL
Select '1234','CompanyX', 'US', '4' UNION ALL
Select '1234','CompanyX', 'US', '8' UNION ALL
Select '1234','CompanyX', 'US', '5' UNION ALL
Select '1234','CompanyX', 'US', '5' UNION ALL
Select '1234','CompanyX', 'US', '11'UNION ALL
Select '1234','CompanyX', 'US', '7' UNION ALL
Select '1234','CompanyX', 'US', '6' UNION ALL
Select '1234','CompanyX', 'EU', '5' UNION ALL
Select '1234','CompanyX', 'EU', '4' UNION ALL
Select '1234','CompanyX', 'EU', '8' UNION ALL
Select '1234','CompanyX', 'EU', '5' UNION ALL
Select '1234','CompanyX', 'EU', '5' UNION ALL
Select '1234','CompanyX', 'EU', '11'UNION ALL
Select '1234','CompanyX', 'EU', '7' UNION ALL
Select '1234','CompanyX', 'EU', '6' UNION ALL
Select '4321','CompanyY', 'US', '5' UNION ALL
Select '4321','CompanyY', 'US', '4' UNION ALL
Select '4321','CompanyY', 'US', '8' UNION ALL
Select '4321','CompanyY', 'US', '5' UNION ALL
Select '4321','CompanyY', 'US', '5' UNION ALL
Select '4321','CompanyY', 'US', '11'UNION ALL
Select '4321','CompanyY', 'US', '7' UNION ALL
Select '4321','CompanyY', 'US', '6' UNION ALL
Select '4321','CompanyY', 'EU', '5' UNION ALL
Select '4321','CompanyY', 'EU', '4' UNION ALL
Select '4321','CompanyY', 'EU', '8' UNION ALL
Select '4321','CompanyY', 'EU', '5' UNION ALL
Select '4321','CompanyY', 'EU', '5' UNION ALL
Select '4321','CompanyY', 'EU', '11'UNION ALL
Select '4321','CompanyY', 'EU', '7' UNION ALL
Select '4321','CompanyY', 'EU', '6'
So the result should look something like this
1234 CompanyX US 0.5
1234 CompanyX EU 0.5
4321 CompanyY US 0.5
4321 CompanyY EU 0.5
November 25, 2011 at 5:26 am
methexis (11/25/2011)
Okay I hope I followed the rules on this!So the result should look something like this
1234 CompanyX US 0.5
1234 CompanyX EU 0.5
4321 CompanyY US 0.5
4321 CompanyY EU 0.5
SELECT a.ACCGRPNUM, a.ACCGRPNAME, a.Region, (a.Pass * 1.0) / b.ACCGRPNUM_Count
FROM (SELECT COUNT(*) AS Pass, ACCGRPNUM, ACCGRPNAME, Region
FROM Derived_Help
GROUP BY ACCGRPNUM, ACCGRPNAME, Region) a
INNER JOIN (SELECT COUNT(*) AS ACCGRPNUM_Count, ACCGRPNUM
FROM Derived_Help
GROUP BY ACCGRPNUM) b ON a.ACCGRPNUM = b.ACCGRPNUM
ACCGRPNUM ACCGRPNAME Region
-------------------- ---------------------------------------- --------------- ---------------------------------------
1234 CompanyX EU 0.500000000000
1234 CompanyX US 0.500000000000
4321 CompanyY EU 0.500000000000
4321 CompanyY US 0.500000000000
Or another way: -
SELECT ACCGRPNUM, ACCGRPNAME, Region, total
FROM (SELECT ACCGRPNUM, ACCGRPNAME, Region,
(COUNT(*) OVER (PARTITION BY ACCGRPNUM, ACCGRPNAME, Region) * 1.0) /
COUNT(*) OVER (PARTITION BY ACCGRPNUM) AS total
FROM Derived_Help) subquery
GROUP BY ACCGRPNUM, ACCGRPNAME, Region, total
Also returns
ACCGRPNUM ACCGRPNAME Region
-------------------- ---------------------------------------- --------------- ---------------------------------------
1234 CompanyX EU 0.500000000000
1234 CompanyX US 0.500000000000
4321 CompanyY EU 0.500000000000
4321 CompanyY US 0.500000000000
November 25, 2011 at 9:00 am
Excellent....it works!
Now I don't just want to take the code and go on my way. When I first attempted to do this I used a 'Where' clause to find out how many locations had a value of 5 or lower, how does this work without defining which locations meet the pass rate and those that dont?!
If I dont understand that then I have not learnt anything!
Cheers
November 25, 2011 at 9:33 am
methexis (11/25/2011)
Excellent....it works!Now I don't just want to take the code and go on my way. When I first attempted to do this I used a 'Where' clause to find out how many locations had a value of 5 or lower, how does this work without defining which locations meet the pass rate and those that dont?!
If I dont understand that then I have not learnt anything!
Cheers
Well, with the first one you'd have to put your WHERE clause on each subquery: -
SELECT a.ACCGRPNUM, a.ACCGRPNAME, a.Region, (a.Pass * 1.0) / b.ACCGRPNUM_Count
FROM (SELECT COUNT(*) AS Pass, ACCGRPNUM, ACCGRPNAME, Region
FROM Derived_Help
WHERE GeoResolutionCode <= '5'
GROUP BY ACCGRPNUM, ACCGRPNAME, Region) a
INNER JOIN (SELECT COUNT(*) AS ACCGRPNUM_Count, ACCGRPNUM
FROM Derived_Help
WHERE GeoResolutionCode <= '5'
GROUP BY ACCGRPNUM) b ON a.ACCGRPNUM = b.ACCGRPNUM
For the second version of the query, you'd do the same thing: -
SELECT ACCGRPNUM, ACCGRPNAME, Region, total
FROM (SELECT ACCGRPNUM, ACCGRPNAME, Region,
COUNT(*) OVER (PARTITION BY ACCGRPNUM, ACCGRPNAME, Region) * 1.0 /
COUNT(*) OVER (PARTITION BY ACCGRPNUM) AS total
FROM Derived_Help
WHERE GeoResolutionCode <= '5') subquery
GROUP BY ACCGRPNUM, ACCGRPNAME, Region, total
The first query works by performing two queries -
SELECT COUNT(*) AS Pass, ACCGRPNUM, ACCGRPNAME, Region
FROM Derived_Help
WHERE GeoResolutionCode <= '5'
GROUP BY ACCGRPNUM, ACCGRPNAME, Region
--And
SELECT COUNT(*) AS ACCGRPNUM_Count, ACCGRPNUM
FROM Derived_Help
WHERE GeoResolutionCode <= '5'
GROUP BY ACCGRPNUM
The GROUP BY essentially means that we're counting the unique records that match. So the first query above will return 4 as the "Pass" for each of the 4 returned records and the second query will return 8 for each of the two records.
We then join these result-sets on the "ACCGRPNUM" so that we can perform your division on them. We can't divide straight away because they are both of datatype INT, which means if the result is a fraction then you'd get a 0. So first we implicitly convert one of them to decimal by multiplying by 1.0, then we divide.
The second query works out the COUNT by partitioning on the same columns we set in the GROUP BY in query one.
SELECT ACCGRPNUM, ACCGRPNAME, Region,
COUNT(*) OVER (PARTITION BY ACCGRPNUM, ACCGRPNAME, Region) AS Pass,
COUNT(*) OVER (PARTITION BY ACCGRPNUM) AS ACCGRPNUM_Count
FROM Derived_Help
WHERE GeoResolutionCode <= '5'
If you execute the above, you'll see that we get 16 rows back, with the Pass and ACCGRPNUM_Count matching the first query.
Now, we need to DISTINCT this result-set, because we don't need all those duplicates. In my original post, I did that in the query, which was wrong. By doing it in the query directly, I changed the result of the COUNTs.
Instead, I should have set them into a subquery, then GROUPed like so: -
SELECT ACCGRPNUM, ACCGRPNAME, Region, Pass, ACCGRPNUM_Count
FROM (SELECT ACCGRPNUM, ACCGRPNAME, Region,
COUNT(*) OVER (PARTITION BY ACCGRPNUM, ACCGRPNAME, Region) AS Pass,
COUNT(*) OVER (PARTITION BY ACCGRPNUM) AS ACCGRPNUM_Count
FROM Derived_Help
WHERE GeoResolutionCode <= '5') subquery
GROUP BY ACCGRPNUM, ACCGRPNAME, Region, Pass, ACCGRPNUM_Count
Now you'll see that we have all of the information from the first query ready to perform our division on it. Again, first we need to multiply one of the INT datatypes by 1.0 to implicitly convert it to decimal.
SELECT ACCGRPNUM, ACCGRPNAME, Region, Pass, ACCGRPNUM_Count,
(Pass*1.0)/ACCGRPNUM_Count AS total
FROM (SELECT ACCGRPNUM, ACCGRPNAME, Region,
COUNT(*) OVER (PARTITION BY ACCGRPNUM, ACCGRPNAME, Region) AS Pass,
COUNT(*) OVER (PARTITION BY ACCGRPNUM) AS ACCGRPNUM_Count
FROM Derived_Help
WHERE GeoResolutionCode <= '5') subquery
GROUP BY ACCGRPNUM, ACCGRPNAME, Region, Pass, ACCGRPNUM_Count
And there you have it. 🙂
November 25, 2011 at 9:48 am
Thats fantastic,
I have already used this method on 2 other queries and it works a treat, not to mention nice and fast!
I cant thankyou enough for your help today.
Have a great weekend!
😎
November 25, 2011 at 10:00 am
methexis (11/25/2011)
Thats fantastic,I have already used this method on 2 other queries and it works a treat, not to mention nice and fast!
I cant thankyou enough for your help today.
Have a great weekend!
😎
Which method did you go for? Did you test both to see which performed best? Just curious 🙂
November 25, 2011 at 10:19 am
Option Number 1, it made more sense in my head!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply