and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select c.Event, c.code, count(1) as calls, total.total_count, count(1) / total.total_count * 100 as pct_of_total from table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
SNO column should be incremented depending upon the total records to be fetched.if i get 4 records then sno numbers should be 1,2,3,4.i dont want to put rownum also in the GROUP BY clause.how to increment the serial number?
SELECT JC.A, ROWNUM SNO, --serial number JC.B, SUM(CHR.AMOUNT), SUM(CHR.FINALAMOUNT), JC.C, JC.D, C.E, JC.F, JC.G FROM CHARGES CHR WHERE JC.B = '12111' AND JC.STATUS = 'INVOICED' GROUP BY JC.A, JC.B, JC.C, JC.D, JC.E, JC.F, JC.G;
But I cannot get the count I have to do a separate Query to get the count here it is How can I put the two together to get my count information and Report information together in one Report???
Select Count(pm.description), mv.R1_State FROM windsoradm.member_mv mv
I am trying to count the number of IDs dropped and enrolled in each unit for each of the 4 terms between their perspective dates. There are 4 Terms and an ID can participate in a unit in any of these terms:
I am trying to count how many IDs enrolled in a unit between those dates and how many doped before the end dateThe ENROL_DATE for each ID in a unit has to be between the given Term Dates to count. Unit KIS has 1 ENROL and one DROP in TERM 1 UNIT POL occurs in TERM 2 and 4 and both DROPUNIT LIN and 1 ENROL and 1 DROP in 2 different TERMS My problem is how do i specify count ENROL and count drop between the Term dates and then group by TERM and UNIT.
I want to list every patients number and name, and the number of treatments given by a certain doctor (e.g doctor name = 'smith'). the problem i am having is that if i specify the doctor is WHERE clause it only gives me the doctors who are under 'smith' but i want all even if 'smith' has given them 0 treatments. below is code that i have so far to view all patients with corresponding data.
select pa.patno, pa.lname, tr.treatno from patient pa full join treatment tr on tr.patno=pa.patno;
We have a table with timestamp column and having millions of records.We want to create a materialized view or query, which can give count based
-on some group by columns from table and -group by on condition (if count > 1000) and -group by on condition (if timestamp range for that group is > 1hr)
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5 23 asdc 1 Location USA NM ABQ Four Seasons 87106 23 asdc 1 Stats 2300 91.7 8.2 85432 23 asdc 1 Audit 1996 June 17 1200 65 affc 2 Location USA TX AUS Hilton 92305 65 affc 2 Stats 5510 42.7 46 9999 65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime 23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200 65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
Department EmpName ____________________________________ Sales empname1 Sales empname2 Marketing empname3 Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total ______________________ Sales 2 Marketing 1 Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM ( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee FROM Employer A, EmployeeInfo B WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2') GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS: Policy id plan name 111 A Plan 111 B Plan 111 Z Plan 112 A Plan 112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3 111 A Plan B Plan Z PLan 112 A Plan Z PLan
I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.
SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT' AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;
how to incorporate counts into my current SQL code that would count food items, and then car items for each person. Once I understand this concept I hope to apply it to my real database (in Oracle) where I am counting medications and problems for each patient.For example using my test data below, the my goal would be to have count columns show that Cathy had 3 food count and a 1 cars item (the count would need to ignore the null values in these columns). I don't really care if the count value repeats in every row for that person.
Select data1.Name, data1.Category,Data1.Results,Case When data1.Category IN ('food') then data1.results end FOOD,Case When data1.Category IN ('Cars')then data1.Results end CARSFROM(Select T1.name Name, null Category, null Results from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNION Select T1.name, 'food', T2.Food from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNIONSelect T1.name, 'cars', T3.Cars from a_Main T1, a_Cars T3 Where T1.cpi_seq = T3.CPI_SEQ)data1where data1.Category is not nullorder by Name,
I want to find the Pass rate for each term by adding up the grades.
PASS = HD/D/PASS FAIL = FAIL and Z means no mark has been recieved yet so i do not want to include it in the calculation.
CREATE TABLE DAN_GR (ID VARCHAR2(8), TERM VARCHAR2(8), GRADE VARCHAR2(8), SUBJECT VARCHAR2 (8), STAT VARCHAR2 (8))
[Code]....
So term 1201 has 1 Pass 1 Fail and a Z (which is not counted) so pass rate is 50% term 1202 has has 1 Pass only so the pass rate is 100% term 1203 has 1 Fail so pass rate is 0% term 1204 has 1 Pass and 2 Fails and i dont count the 3rd fail because the stat is 'APSENT' i only want to count the once where stat is 'PRESENT'
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for 64-bit Windows: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
SQL> with t 2 as 3 ( 4 select 1 id, 12 compid, 1 rel_type, null enddt from dual union all 5 select 1, 13, 1, to_date('31.12.1993','dd.mm.yyyy') from dual union all 6 select 1, 14, 1, to_date('12.06.1996','dd.mm.yyyy') from dual union all 7 select 1, 15, 1, to_date('23.04.2003','dd.mm.yyyy') from dual union all [code].......
I want to find the the latest compid for individual types 1 and 2 only within a list of ids. Latest compid is defined as the compid associated with the latest end date (null is treated as the latest end date).
So in the above example for rel_type = 1, compid : 12 is latest, for rel_type : 2, compid : 6 is latest.
Datatype for the tables are :
Id : Number compid : Number Rel_Type : Number enddt : Date
The communication table has around 1 billion rows which is partitioned by a date column which is not used in the query. The hierarchy table has only 10000 rows. rewrite the query if its possible.Its trying to find the count of customers[distinct] which are in the same campaign.
select h.brand_cd, h.campaign_name, h.campaign_start_dt, h.campaign_name,h.campaign_desc,h.subject, count(distinct cc.customer_id) from communication cc,hierarchy h where cc.campaign_hierarchy_id = h.campaign_hierarchy_id and h.brand_cd in ('ABY','AYO') AND H.CAMPAIGN_OWNER = 'WONDER' and h.campaign_start_dt >'1-JUL-2011' group by h.brand_cd, h.campaign_name, h.campaign_start_dt, h.campaign_desc,h.subject;
Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.
Table:
CREATE TABLE N_B_S_1 ( NUM NUMBER, VAL VARCHAR2(20) )
Data:
INSERT INTO N_B_S_1 VALUES(1,'A,B,C'); INSERT INTO N_B_S_1 VALUES(2,'D'); INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK'); INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV'); INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM'); COMMIT;
i am trying to identify the pro's/con's of using multiple ASM Diskgroup. I understand oracle recommends/best practice is to have 2 DG (one data and one flash) and you can place multiple copies of control files/online redo logs(and thats the way i want to go). But would that same be true if i use different set of DISK. For example we have multiple RAID 10 devices and multiple of SSD devices for us that we can use for this ASM instance. And i was thinking to create 2 more Disk group (call it DG_SYS1 and DG_SYS2) and use that to put my online redo logs, control file and temp and system table space there. i understand in a standalone system(where regular file system is being used), they(online redo/ control file) are usually on there own drives, but with ASM when i am already using external RAID 10 config + ASM striping i assume the IO would faster or am i better of using the SSD that i can have for my redo/control? What would be the pro's/cons of it (besides managing multiple DG)..
