October 14, 2011 at 7:10 pm
hi! a little help here please.
i have here 3 tables, the cluster table, field table and a variety table..
here are the conditions:
-1 variety can be planted in many different fields.
-many fields belong to one cluster
how can i get the most number of variety planted in a particular cluster?
here's my code but it returns an error.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
please help me on this. thanks.
select Max(COUNT(varietyid)) [VarietyCount], varietyid, clusterid
from farm_field group by varietyid, clusterid
October 14, 2011 at 8:47 pm
NaDi (10/14/2011)
hi! a little help here please.i have here 3 tables, the cluster table, field table and a variety table..
here are the conditions:
-1 variety can be planted in many different fields.
-many fields belong to one cluster
how can i get the most number of variety planted in a particular cluster?
here's my code but it returns an error.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
please help me on this. thanks.
select Max(COUNT(varietyid)) [VarietyCount], varietyid, clusteridfrom farm_field group by varietyid, clusterid
I'm not quite sure what you're going for, do you want 1 row per cluster, returning the variety that was planted the most, and the number of times it was planted?
untested:
;with cluster_counts as
(select clusterid,
varietyid,
count(*) varietyCount
from farm_field
group by varietyid,
clusterid
),
ranked_cluster_counts as
(select varietyCount,
clusterid,
varietyid,
row_number() over (partition by clusterid,
order by varietycount desc) row_rank
from cluster_counts
)
select VarietyCount,
varietyId,
clusterId
from ranked_cluster_counts
where row_rank = 1
Some schema for the three tables, sample data and expected results would be really helpful...
October 14, 2011 at 9:04 pm
yes, that's what I want. thank you for this. 🙂
October 14, 2011 at 9:07 pm
If you need the cluster having the maximum value of count of variety id , you could try it below:
select Max (s.varietycount)as Max_cluster from
( select COUNT(varietyid)as varietycount , clusterid from farm_field GROUP BY Clusterid) as S
If that isn't the business target , please let me know how output should be like ..?
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 15, 2011 at 6:33 pm
Performace Guard (Shehap) (10/14/2011)
If you need the cluster having the maximum value of count of variety id , you could try it below:select Max (s.varietycount)as Max_cluster from
( select COUNT(varietyid)as varietycount , clusterid from farm_field GROUP BY Clusterid) as S
...
That's going to tell you how many fields the cluster with the most fields contains. If you wanted to know which cluster contained the most varieties - which seems more like what you were aiming for - you could do something like this:
SELECT z.clusterid, z.NumVarieties
FROM (SELECT y.clusterid, y.NumVarieties, ROW_NUMBER() OVER (ORDER BY y.NumVarieties DESC) RowNumber
FROM (SELECT x.clusterid, COUNT(DISTINCT x.varietyid) NumVarieties
FROM farm_field x
GROUP BY x.clusterid) y) z
WHERE z.RowNumber = 1;
October 15, 2011 at 6:53 pm
SpringTownDBA,
Any particular reason you chose CTEs over, say, derived tables? I guess I'm wondering if there are performance implications, or if that was just your personal preference. For me, in a situation like this where the logic of the subqueries is pretty simple, I prefer using derived tables:
SELECT z.clusterid, z.varietyid, z.FieldCount
FROM (SELECT y.clusterid, y.varietyid, y.FieldCount, RANK() OVER (PARTITION BY y.clusterid ORDER BY y.FieldCount DESC) RankNumber
FROM (SELECT x.clusterid, x.varietyid, COUNT(*) FieldCount
FROM farm_field x
GROUP BY x.clusterid, x.varietyid) y) z
WHERE z.RankNumber = 1
Of course in this version, if the greatest number of fields that any one variety is planted in in a cluster is five, and there are two varieties that are both planted in five fields in the cluster, the query will return them both. I don't know if that's what the OP wanted, but if not, change RANK() to ROW_NUMBER(). In that case though, which of the two varieties you get will be essentially random.
October 15, 2011 at 7:16 pm
Personal preference. Cte's format better with sql prompt, and if I build the query incrementally, cte's are easier to use.
Honestly, the only queries I've written recently have either been against dmv's, or really large ugly ones that exceed the skill level of our .net developers.
October 15, 2011 at 7:28 pm
SpringTownDBA (10/15/2011)
[...]Honestly, the only queries I've written recently have either been against dmv's, or really large ugly ones that exceed the skill level of our .net developers.
Ha! That's not saying much. I don't know about you, but in my experience most developers couldn't query their way out of a paper bag. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply