Hello,


I've written a query to get me the metrics (record counts) in the
system.

SELECT sites.total, main.mains, sub.subs, ann.announcements,
ass.assignments, res.resources, pc.channels, pi.podcasts,
loc.locations, u.users FROM
(SELECT count(site_pk) total FROM site) sites,
(SELECT count(site_pk) mains FROM site WHERE mainsite_id = site_id)
main,
(SELECT count(site_pk) subs FROM site WHERE mainsite_id != site_id)
sub,
(SELECT count(announce_pk) announcements FROM announcements) ann,
(SELECT count(assign_pk) assignments FROM assignments) ass,
(SELECT count(resource_pk) resources FROM resources) res,
(SELECT count(podcast_channel_pk) channels FROM podcast_channel) pc,
(SELECT count(podcast_items_pk) podcasts FROM podcast_items) pi,
(SELECT count(distinct(location_code)) locations FROM site) loc,
(SELECT count(tracking_id) users FROM users) u


I then modified that to give me the counts per user.

SELECT u.tracking_id, u.full_name,
(SELECT site_id FROM site WHERE tracking_id = u.tracking_id AND
site_id = mainsite_id) mainsite,
(SELECT count(site_pk) total FROM site WHERE tracking_id =
u.tracking_id) sites,
(SELECT count(announce_pk) announcements FROM announcements WHERE
tracking_id = u.tracking_id) announcements,
(SELECT count(assign_pk) assignments FROM assignments WHERE
tracking_id = u.tracking_id) assignments,
(SELECT count(resource_pk) resources FROM resources WHERE tracking_id
= u.tracking_id) resources,
(SELECT count(podcast_channel_pk) channels FROM podcast_channel WHERE
tracking_id = u.tracking_id) channels,
(SELECT count(podcast_items_pk) podcasts FROM podcast_items WHERE
tracking_id = u.tracking_id) podcasts
FROM users u


Now I'm trying to ORDER that query so that I can get the top users for
the record counts of sites, announcements, assignments, etc.

The following query returns the same as above.

SELECT x.tracking_id, x.full_name, x.mainsite, x.sites,
x.announcements, x.assignments, x.resources, x.channels, x.podcasts
FROM
(SELECT u.tracking_id, u.full_name,
(SELECT site_id FROM site WHERE tracking_id = u.tracking_id AND
site_id = mainsite_id) mainsite,
(SELECT count(site_pk) total FROM site WHERE tracking_id =
u.tracking_id) sites,
(SELECT count(announce_pk) announcements FROM announcements WHERE
tracking_id = u.tracking_id) announcements,
(SELECT count(assign_pk) assignments FROM assignments WHERE
tracking_id = u.tracking_id) assignments,
(SELECT count(resource_pk) resources FROM resources WHERE tracking_id
= u.tracking_id) resources,
(SELECT count(podcast_channel_pk) channels FROM podcast_channel WHERE
tracking_id = u.tracking_id) channels,
(SELECT count(podcast_items_pk) podcasts FROM podcast_items WHERE
tracking_id = u.tracking_id) podcasts
FROM users u) x


If I add

ORDER BY x.sites ASC

I get 'ORA-01427: single-row subquery returns more than one row'

Cause: The outer query must use one of the keywords ANY, ALL, IN,
or NOT IN to specify values to compare because the subquery returned
more than one row.

Action: Use ANY, ALL, IN, or NOT IN to specify which values to
compare or reword the query so only one row is retrieved.


I'm not sure how or where to use ANY,ALL,IN etc in the syntax and
everything I've tried isn't working.


Thanks

On Jun 17, 10:33*am, arkarrel <carrollusi... (AT) gmail (DOT) com> wrote:
Didn't look too close, 'cause it sounds like a faq:
http://www.orafaq.com/faq/how_does_o...s_from_a_table

jg
--
@home.com is bogus.
http://netzpolitik.org/2009/the-dawn...ip-in-germany/

On Jun 17, 1:08*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:


Not looking for the top 10 rows. I simply want to be able to order the
entire result by certain fields generated by the query. So that I can
reorder/sort by Most Sites, Most Announcements, etc or do an order by
desc on all, and get the top results based on the counts.

On Jun 18, 8:55*am, arkarrel <carrollusi... (AT) gmail (DOT) com> wrote:
If you want more detailed help, you would be better off giving DDL and
sample data so people can replicate what you are trying to do.

But this might help, if your problem is getting to the right level of
detail in the query: http://asktom.oracle.com/pls/asktom/...D:995030557145

There are a number of other possible answers to ordering by certain
generated fields. Maybe even http://www.oracle.com/technology/ora...4tech_sql.html
which makes my brain explode.

jg
--
@home.com is bogus.
http://www.itbusiness.ca/it/client/e...=true&id=53500

think i got this fixed.

some bad data, had multiple rows where 1 was expected.

thanks!

arkarrel schreef:
I still wonder how an 'order by' could have caused the error to pop up,
where the query without order by did not report errors

Shakespeare