Sitemap

Amicuk Programming Answers

How to make the query in one single query? No Union please....

2015-10-11   Views:0

Advertisement

Hi, I have sets of around 18 queries which is finding counts in many scenarios. I wanted to have a single query for this. which diplays the result in a single row.Is there a way , I heared analytical functions are good options ,but I am new to Oracle

Hi,
I have sets of around 18 queries which is finding counts in many scenarios. I wanted to have a single query for this. which diplays the result in a single row.Is there a way , I heared analytical functions are good options ,but I am new to Oracle . Please help me.
select count(*) total_count from emp; --100
select coun(*) MGR_count from emp where job='MGR'-- 10
select count(*) dept_count from emp where deptno in(10 ,20)-- 75
output..
TOTAL_COUNT     MGR_COUNT      DEPT_COUNT
100                                   10             75
Thanks

The replay answer
Advertisement
This:
select count(*) total_Count
      ,count(decode(job,'MGR',1,null)) mgr_count
      ,count(case when deptno in (10,20) then
               1
             else
               null
             end
            ) dept_count
from emp;
untested

Go to See the other 3 answers

Query with union

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have three table: Candidates: (candidate_id primary key) Purhcase_orders: (Po_number primary key) Bids: (bid_number primary key) Just wanted to retrieve candidate information like candidate_id, first_name etc, along with count of purchase order[More]

Returning a count from a query using Union

Category:DefaultRelease time:2015-10-11Views:130

Hi. I'm attempting to select a count from this query and then display the total in a message, but I'm getting a 'Too many rows returned' (ORA-01422). Can anyone tell me how I can achieve a total for this query?          SELECT nvl(count(*),0)       [More]

Select query with UNION clause in database adapter

Category:DefaultRelease time:2015-10-11Views:130

Friends, I have got a SQL query with two UNION clause withing it. like select a,b,c from a union select a,b,c from b The schema generated is like below in sequence <element>a</element> <element>b</element> <element>c</elem[More]

Query with Union to Show Ship to of Both Supplier & Customer BP's

Category:DefaultRelease time:2015-10-11Views:130

Hi Guys, I need to do a query that I can use in a formatted search, that will allow me to select from a list that shows both BP Ship to Addresses from The Customer and Supplier Tables. I know I have to use a union, however I have no clue where to sta[More]

Command Query using UNION

Category:DefaultRelease time:-0001-11-30Views:130

Hello, I may be barking up the wrong tree and asking this question in the wrong forum so please accept my apologies if this is the case. I have access to a MYSql database through a ODBC connection and would like to create a report based on four table[More]

Writing query using UNION Operator

Category:DefaultRelease time:-0001-11-30Views:130

Question - (1) Write an SQL Statement to list the following items: Customer ID, Customer Name, number of invoices, sum of total for invoices. Ensure that all customers are returned in the result set. Answer for the above is written as below by one pe[More]

Query using Union All and CTEs is slow

Category:DefaultRelease time:-0001-11-30Views:130

TypePatient [ednum] int NOT NULL,  PK [BackgroundID] int NOT NULL, FK [Patient_No] varchar(50) NULL, FK [Last_Name] varchar(30) NULL, [First_Name] varchar(30) NULL, [ADateTime] datetime NULL, Treat [ID] int NOT NULL, PK [Ednum] numeric(10, 0) NOT NUL[More]

Sql query using union

Category:DefaultRelease time:-0001-11-30Views:130

hi i need the query to fetch the data ... i have two table 1. plan 2. voidplan each table has id uniquely, and each table has rep_id but it is not unique. i retrieved the data from both table using union. now i need the data for the rep who is in bot[More]

Where to place ORDER BY clause when a query includes UNION

Category:DefaultRelease time:-0001-11-30Views:130

I have a query that has UNION in it. could you please tell me where to place the ORDER BY clause. Because it's throwing an error if i place the ORDER BY clause at the endBecause you are using the UNION set operator, you need to either specifically li[More]

Urgent! How to use push_pred to optimize query with UNION in 10g?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, We are facing slow query performance in 10g database. Appreciate if anyone could advise on how to optimize the performance by using push pred? Or is there any other ways. Thanks in advance. Cheers, SCdont post duplicate postRead other 2 answers[More]

Query with UNION option

Category:DefaultRelease time:-0001-11-30Views:130

Hi , I have one partition table.The partitions are created datewise.Now when i have to fire select query on that talble i am firing below querry Select count(1) from table A where createdate between ('01-JAN-2012 00:00:00','DD-MON-YYYY HH24:MI:SS') A[More]

Hot
Hi there.  My project has 7 different resources with various Resource Names ending characters.  I've automatically color coded the bars on my Gantt Chart based on resource allocation by using Flags 1-7, Custom Fields, and the formula IIf(Left([Resour [More]
Hi, I built a query with 4 tables inside (load from Oracle DB and two of them are quite big, more than millions of rows). After filtering, I tried to build relationships between tables using Table.Join formula. However, the process took extremly long [More]
Hello all...I'm using FrameMaker 7 to produce a .ps file, then creating a PDF with Acrobat Distiller v8. The newsletter we're printing has images that are darker than the jpeg's used, they're displaying fine in Frame and the PDF. This process had bee [More]
Hello all, I started to have a few random problems with my iPod touch less than 3 weeks after getting it. I always took very good care it (I got the 16gb and it cost WAY to much NOT to take good care of it). Well, after trying to fix it, it completel [More]
i have many ss ejbs running in wls 6.1 under domain1. i have one ss ejb (called Submit) running in wls 6.1 under domain2 which makes calls to the beans in domain1. this is working fine. i'm migrating domain2 to use wls8.1, and am getting all kinds of [More]
I am having a reoccuring problem when entering information into a downlaoded PDF form that my keyboard types the wrong character into the PDF.  It happens very randomly and without any apparent pattern. For instance, I will beattempting to type a dol [More]
I am trying to install the OAP and need to enable or configure the wireless portion of it. I got this cpio file from Oracle site (as_linux_x86_101300_disk1.cpio & as_linux_x86_101300_disk2.cpio) and installed the OAP. I don't see any option to enable [More]
what should i doHi Mr.Acevedo329! I have an article here for you that can help you troubleshoot the issues you are experiencing. That article can be found right here: iPod troubleshooting basics and service FAQ http://support.apple.com/kb/ts1382 Spec [More]
Does Internet Explorer (specifically a version 7 through 9) work on a MAC 10.8.4? I would like to access documents from my work that are only supported on Internet Explorer.Joni2b wrote: ...I would like to access documents from my work that are only [More]
Hi all, I just upgraded to Ubuntu 6.10 and discovered that SQL Developer does not start. It fails with next message: [email protected]:~/opt/sqldeveloper$ sh sqldeveloper Oracle SQL Developer 1.0 Copyright (c) 2005 Oracle Corporation. All Rights Reserved. [More]