• RELEVANCY SCORE 4.07

    DB:4.07:Count Unique Values And Unique Text Comparing Two Columns pj




    I have two colums, one is an ID and the other is a Name. I want to count only the Unique ID and Unique Name, without counting Multiple IDs of the same Name

    Example...

    Column
    A B
    ID Name
    1111 Test
    1111 Test 1
    1111 Data
    1112 Data 1
    1112 Data 1
    1112 Test
    1113 Test
    1113 Test
    1113 Test
    ...

    Count should be 6.... Dont want to count multiple Names or blanks of the same ID

    Thanks so much in advance!!

    DB:4.07:Count Unique Values And Unique Text Comparing Two Columns pj

    Hi,

    May I request you to post your question in the Comments section of this link -

    http://www.ashishmathur.com/count-uniques-with-conditions/#comments

    Thank you.

  • RELEVANCY SCORE 3.90

    DB:3.90:Count Unique Values Two Columns Of Data pc




    I need to count the X's in column B only if the values in Column A are unique.
    The example below would resultin a count of 2 (one X for 5262, and one X for 6520)
    I triedseveral formulas (sumproduct, countifs) but can't get the correct answer.

    5262
    X

    6520

    6520
    X

    6520
    X

    6520
    X

    6520
    X

  • RELEVANCY SCORE 3.77

    DB:3.77:Can Anybody Solve This Problem da




    Hi,

    I have 1 Billions of records in my table and around 400 columns, I need data in this format

    Detail 1.5 Month 3.5 Month 6.5 Month 12.5 Month all month
    Disc.. 10 15 16 23 50
    Gas.. 8 7 6 5 30

    I am using this query

    select 'DISCOVER_REGULAR',
    count(unique(case when last_order_dt=sysdate-45 then individual_id end)) one_month_10,
    count(unique(case when last_order_dt=sysdate-105 then individual_id end)) three_month_10,
    count(unique(case when last_order_dt=sysdate-195 then individual_id end)) six_month_10,
    count(unique(case when last_order_dt=sysdate-380 then individual_id end)) twelve_month_10,
    count(unique(individual_id)) all_active_DISCOVER
    from lifephase_trends where last_role_cd in('1','2','3') and DISCOVER_REGULAR='Y'
    union all
    select 'GAS_CARD',
    count(unique(case when last_order_dt=sysdate-45 then individual_id end)) one_month_11,
    count(unique(case when last_order_dt=sysdate-105 then individual_id end)) three_month_11,
    count(unique(case when last_order_dt=sysdate-195 then individual_id end)) six_month_11,
    count(unique(case when last_order_dt=sysdate-380 then individual_id end)) twelve_month_11,
    count(unique(individual_id)) all_active_gas
    from lifephase_trends where last_role_cd in('1','2','3') and DISCOVER_REGULAR='Y'

    I need to extract all 387 columns then I have to use 387 time union all
    is there any way to get the output in single FROM?

    DB:3.77:Can Anybody Solve This Problem da

    It's not so much easier, but a bit more structured:

    with normalized_lifephase_trends as
    (
    select 'DISCOVER_REGULAR' detail, individual_id, last_order_dt, last_role_cd from lifephase_trends where DISCOVER_REGULAR='Y' union all
    select 'GAS_CARD' , individual_id, last_order_dt, last_role_cd from lifephase_trends where GAS_CARD='Y' union all
    ...
    )
    select detail,
    count(unique(case when last_order_dt=sysdate-45 then individual_id end)) one_month_10,
    count(unique(case when last_order_dt=sysdate-105 then individual_id end)) three_month_10,
    count(unique(case when last_order_dt=sysdate-195 then individual_id end)) six_month_10,
    count(unique(case when last_order_dt=sysdate-380 then individual_id end)) twelve_month_10,
    count(unique(individual_id)) all_active_DISCOVER
    from normalized_lifephase_trends where last_role_cd in ('1','2','3')
    group by detailEdited by: hm on 11.10.2010 10:20

  • RELEVANCY SCORE 3.70

    DB:3.70:Counting Unique Values Based On 2 Columns 7d


    I have been struggling all day to find the correct formula to use when trying to count unique values using different columns. I have a multiple column spreadsheet and i would like to identify the number of unique values (text alpha) in column D (job
    #) that has a specific status which is in column F. For example, see below, the working status has 3 unique job#'s. Can someone help please? Thank you

    Job# Status
    100 Initial
    201 Working
    634 Working
    201 Working
    978 Completed
    978 Working

  • RELEVANCY SCORE 3.65

    DB:3.65:Excel 2007 - Trying To Make 2 Unique Columns Into 1, All Values Preserved mj


    Hi All

    What I'm trying to do feels really simple, but I haven't found a way of doing it yet! I have 2 columns each containing a number of unique values, and I want to have a way of combining these into 1 column. I realise from my research that I'm probably not using
    the correct term for what I want to do; I'm certainly not merging the data, possibly I'm combining or consolidating it. Basically:

    Column A Column B
    1 abc def
    2 xyz ghi

    goes into

    Column C
    1 abc
    2 xyz
    3 def
    4 ghi

    Does this make sense?

    Many thanks,
    Paul.

    DB:3.65:Excel 2007 - Trying To Make 2 Unique Columns Into 1, All Values Preserved mj

    That's perfect as it allows for C and D to be empty, which is how the report will be. Once again thanks for your help.

  • RELEVANCY SCORE 3.50

    DB:3.50:Getting Distinct Items z3


    Hi all,

    I want to know about the DISTINCT keyword..

    I have a requirement that I need to get unique items based on two columns (there are 5 columns totally).

    SELECT A1, A2, A3, A4, A5 FROM A_TABLE;

    In this the data should be retrieved by unique items for A1 and A2, and I want to get all other columns also in a single query.

    It should be like SELECT DISTINCT (A1, A2), A3, A4, A5 FROM A_TABLE;

    This query wont work, so I want to know that is there any options for retrieving values like this by a single query.

    Thanks in advance
    Rajeev R.

    DB:3.50:Getting Distinct Items z3

    Rajeev_India wrote:
    My table data :

    A1 A2 A3 A4 A5 A6

    1 2 3 4 5 1
    2 2 3 5 4 2
    2 2 3 5 4 3
    3 1 3 6 7 4
    3 1 3 6 7 5
    3 4 3 6 7 6

    I want result like

    1 2 3 4 5 1
    2 2 3 4 5 2
    3 1 3 6 7 4
    3 4 3 6 7 6How does:

    2 2 3 5 4 2
    2 2 3 5 4 3

    Become:
    2 2 3 4 5 2

    ?

    If you're not bother about what is in the columns 3,4,5 and 6 then, as already mentioned, what's the point of retrieving/displaying them? You must have some logic behind what you want showing there?

    e.g. you could use max...

    SQL ed
    Wrote file afiedt.buf

    1 WITH t as (select 1 as id1, 1 as id2, 1 as data1, 1 as data2, 1 as data3 from dual union all
    2 select 1,1,2,3,4 from dual union all
    3 select 1,1,5,7,3 from dual union all
    4 select 1,2,1,3,5 from dual union all
    5 select 1,2,2,2,2 from dual union all
    6 select 2,1,9,8,7 from dual union all
    7 select 2,2,8,7,6 from dual union all
    8 select 2,2,6,5,4 from dual)
    9 select id1, id2, max(data1) data1, max(data2) data2, max(data3) data3
    10 from t
    11 group by id1, id2
    12* order by id1, id2
    SQL /

    ID1 ID2 DATA1 DATA2 DATA3
    ---------- ---------- ---------- ---------- ----------
    1 1 5 7 4
    1 2 2 3 5
    2 1 9 8 7
    2 2 8 7 6

    SQLor sum...

    SQL ed
    Wrote file afiedt.buf

    1 WITH t as (select 1 as id1, 1 as id2, 1 as data1, 1 as data2, 1 as data3 from dual union all
    2 select 1,1,2,3,4 from dual union all
    3 select 1,1,5,7,3 from dual union all
    4 select 1,2,1,3,5 from dual union all
    5 select 1,2,2,2,2 from dual union all
    6 select 2,1,9,8,7 from dual union all
    7 select 2,2,8,7,6 from dual union all
    8 select 2,2,6,5,4 from dual)
    9 select id1, id2, sum(data1) data1, sum(data2) data2, sum(data3) data3
    10 from t
    11 group by id1, id2
    12* order by id1, id2
    SQL /

    ID1 ID2 DATA1 DATA2 DATA3
    ---------- ---------- ---------- ---------- ----------
    1 1 8 11 8
    1 2 3 5 7
    2 1 9 8 7
    2 2 14 12 10

    SQLor average...

    SQL ed
    Wrote file afiedt.buf

    1 WITH t as (select 1 as id1, 1 as id2, 1 as data1, 1 as data2, 1 as data3 from dual union all
    2 select 1,1,2,3,4 from dual union all
    3 select 1,1,5,7,3 from dual union all
    4 select 1,2,1,3,5 from dual union all
    5 select 1,2,2,2,2 from dual union all
    6 select 2,1,9,8,7 from dual union all
    7 select 2,2,8,7,6 from dual union all
    8 select 2,2,6,5,4 from dual)
    9 select id1, id2, sum(data1) data1, sum(data2) data2, sum(data3) data3
    10 from t
    11 group by id1, id2
    12* order by id1, id2
    SQL /

    ID1 ID2 DATA1 DATA2 DATA3
    ---------- ---------- ---------- ---------- ----------
    1 1 8 11 8
    1 2 3 5 7
    2 1 9 8 7
    2 2 14 12 10

    SQLetc. etc. etc.

  • RELEVANCY SCORE 3.38

    DB:3.38:Sas Data Transformation dc



    Hi,

    I have a sas dataset like the following.

    IDENTIFIER COMPUTATION

    UNIQUE 001

    NAME JOHN

    DESCRIBE XXXX

    DEFINE XXXX

    DEF001 XXXX

    DEF002 XXXX

    REPORT XXXX

    DECISION XXXX

    DEC001 XXXX

    DEC002 XXXX

    UNIQUE 002

    NAME GEORGE

    DESCRIBE XXXX

    DEFINE XXXX

    DEF001 XXXX

    DEF002 XXXX

    REPORT XXXX

    DECISION XXXX

    DEC001 XXXX

    DEC002 XXXX

    UNIQUE 003

    NAME SAM

    DESCRIBE XXXX

    DEFINE XXXX

    DEF001 XXXX

    DEF002 XXXX

    REPORT XXXX

    DECISION XXXX

    DEC001 XXXX

    DEC002 XXXX

    I would like create a table(one row of will contain from UNIQUE to UNIQUE) with the columns as UNIQUE, NAME, DESCRIBE, REPORT, DEFINE(concatinating DEF001, DEF002 etc), DECISION(concatinating DEC001, DEC002) and eventually upload this to sql server. The values of these columns should be assigned from COMPUATION variable.

    The table should like something like the following

    NAME DESCRIBE DEFINES DECISION REPORT

    JOHN XXXX conacatinate(def001, def002 etc) concatinate(Bdec001:/bXXXX,bBR dec002:/bXXX) XXXX

    I really appreciate any ideas. Thank you.

    DB:3.38:Sas Data Transformation dc


    Yes, I need breaks before and after. You are right, BR should introduce a break. Thank you Tom.

  • RELEVANCY SCORE 3.36

    DB:3.36:Foreign Key Alter Table Query Problem 3p


    HAI ALL,

    ANY SUGGESTION PLEASE?

    SUB: foreign key ALTER TABLE QUERY PROBLEM

    I WANT TO CREATE foreign key AND ALTER TABLE:

    1.TABLE:HAEMATOLOGY1
    COLUMN:HMTLY_PATIENT_NUM
    WITH
    TABLE:PATIENTS_MASTER1
    COLUMN:PATIENT_NUM (THAT IS PRIMARY AND UNIQUE KEY)

    1.TABLE:HAEMATOLOGY1
    COLUMN:HMTLY_TEST_NAME
    WITH
    TABLE:TESTS_MASTER1
    COLUMN:TEST_NAME ((THAT IS UNIQUE KEY)
    ---------------

    SQL+ QUERY GIVEN:
    -----------
    ALTER TABLE HAEMATOLOGY1
    Constraint SYS_C002742_1 foreign key (HMTLY_PATIENT_NUM)
    references PATIENTS_MASTER1(PATIENT_NUM);

    ERROR at line 2:
    ORA-01735: invalid ALTER TABLE option

    NOTE: CONSTRAINTS NAME: SYS_C002742_1 TAKEN FROM TABLE DETAILS AT ORACLE ENTP. MGR.
    ---------
    ALTER TABLE HAEMATOLOGY1
    Constraint SYS_C002735_1 foreign key (HMTLY_TEST_NAME)
    references TESTS_MASTER1(TEST_NAME);

    ERROR at line 2:
    ORA-01735: invalid ALTER TABLE option

    NOTE: CONSTRAINTS NAME: SYS_C002735_1 TAKEN FROM TABLE DETAILS AT ORACLE ENTP. MGR.

    ==============

    4 TABLES FOR CLINICAL LAB FOR INPUT DATA AND GET REPORT ONLY FOR TESTS MADE FOR PARTICULAR

    PATIENT.

    TABLE1:PATIENTS_MASTER1
    COLUMNS:PATIENT_NUM, PATIENT_NAME,

    VALUES:
    PATIENT_NUM
    1
    2
    3
    4
    PATIENT_NAME
    BBBB
    GGGG
    KKKK
    PPPP
    ---------------
    TABLE2:TESTS_MASTER1
    COLUMNS:TEST_NUM, TEST_NAME

    VALUES:
    TEST_NUM
    1
    2
    TEST_NAME
    HAEMATOLOGY
    DIFFERENTIAL LEUCOCYTE COUNT
    -------------

    TABLE3:HAEMATOLOGY1
    COLUMNS:
    HMTLY_NUM,HMTLY_PATIENT_NUM,HMTLY_TEST_NAME,HMTLY_RBC_VALUE,HMTLY_RBC_NORMAL_VALUE

    VALUES:
    HMTLY_NUM
    1
    2
    HMTLY_PATIENT_NUM
    1
    3
    MTLY_TEST_NAME
    HAEMATOLOGY
    HAEMATOLOGY
    HMTLY_RBC_VALUE
    5
    4
    HMTLY_RBC_NORMAL_VALUE
    4.6-6.0
    4.6-6.0
    ------------

    TABLE4:DIFFERENTIAL_LEUCOCYTE_COUNT1
    COLUMNS:DLC_NUM,DLC_PATIENT_NUM,DLC_TEST_NAME,DLC_POLYMORPHS_VALUE,DLC_POLYMORPHS_

    NORMAL_VALUE,

    VALUES:
    DLC_NUM
    1
    2
    DLC_PATIENT_NUM
    2
    3
    DLC_TEST_NAME
    DIFFERENTIAL LEUCOCYTE COUNT
    DIFFERENTIAL LEUCOCYTE COUNT
    DLC_POLYMORPHS_VALUE
    42
    60
    DLC_POLYMORPHS_NORMAL_VALUE
    40-65
    40-65
    -----------------

    THANKS
    RCS
    E-MAIL:bimpexrcs@yahoo.co.in
    --------

    DB:3.36:Foreign Key Alter Table Query Problem 3p

    HAI, Nopparat V.

    THANK YOU, NOW WORKING.
    RCS
    ------------

    SQL ed
    Wrote file afiedt.buf

    1 ALTER TABLE HAEMATOLOGY1
    2 ADD Constraint PNUMFK foreign key (HMTLY_PATIENT_NUM)
    3* references PATIENTS_MASTER1(PATIENT_NUM)
    SQL /

    Table altered.

  • RELEVANCY SCORE 3.35

    DB:3.35:Query List With Large Number Of Items To Perform Aggregation d1


    I have a scenario where there is one list with 3 columns : Col1, Col2, Col3
    For each value of Col1 there would be 100 unique Col2 values. And for each Col2 values there would be 1000 unique Col3 values.
    List has more than 1,000,000 items.
    I would like to perform a query that for a given Col1 value, I need to display a list of unique Col2 Values related to Col1 and count of Col3 values.
    For example, for a given dataset as shown below
    --------------------------------------
    | Col1 | Col2 | Col3 |
    --------------------------------------
    | Val1 | Cl2_1 | Cl3#1 |
    | Val1 | Cl2_1 | Cl3#2 |
    | Val1 | Cl2_2 | Cl3#3 |
    --------------------------------------
    My output should be something like :
    | Val1 |
    -------------------------
    | Col2 | Col3 |
    -------------------------
    | Cl2_1 | 2 |
    | Cl2_2 | 1 |
    -------------------------
    What are my possible options?

    DB:3.35:Query List With Large Number Of Items To Perform Aggregation d1

    Try using Caml or linq query to retrieve the data from list

  • RELEVANCY SCORE 3.32

    DB:3.32:Help To Do Data Mining And Transformation... 3d


    I have a specific task to accomplish and I am wondering if Oracle Data Mining is the correct tool to use, and if not what possibly might be. Here is brief description:

    I have a table with about 500 millions rows of data per day, transactional internet traffic data. It contains about 20 columns/dimensions. The requirement is to transform this flat data into a new table that contains (as one column each) each unique variation of those dimension values recorded.

    So for example, if we have 3 dimensions of say gender, age and zip code we would determine each unique combination of those found in the actual data and write out x number of columns to identify them and store a count value for each one. The count will just tell us how many of that combination was found in the data, and the end result will be of course an aggregated table for fast querying on all observed dimensions.

    For performance reasons we want to pass through the data only only.

    We tried cubes but this takes too long (because it also tries to build out all the non-observed combinations), and we know we could try a code approach but fear this may take too long also. The problem is more of a performance one of course, with that many rows and possible combinations to consider.

    Any ideas?

    Thanks in advance.

    DB:3.32:Help To Do Data Mining And Transformation... 3d

    After doing some research I realize what I need is a cube, but one that does not contain every single dimension combination but only those that actually exist (to speed up the creation time and reduce storage space). Is this something Oracle supports? Anyone?

  • RELEVANCY SCORE 3.26

    DB:3.26:Find Duplicate Values In A Table Based On Combination Of Two Columns. zx


    In table which have millions of records -
    I have a Table B under Schema A and i have to find the duplicate records in a table. My Table B uses the combination of columns to identify the unique values.
    Scenario is something like this-
    One SV_Id can have multiple SV_Details_Id and i have to find out if there is any duplicate in the table. (I have to use combination of columns to identify the unique values and there is no Primary Key, Foreign Key or Unique Key on the table)

    then wrote the following query -
    select SV_Id,SV_Details_Id count (*) from SchemaA.TableB group by SV_Id,SV_Details_Id having Count (*) 1 ;

    Is it correct as after firing the above query it is returning the rows which is not matching to the duplicate.

    kindly help me.
    Looking forward for some guidance -
    Thanks in advance.

    DB:3.26:Find Duplicate Values In A Table Based On Combination Of Two Columns. zx

    What is the desired output??

    Do you want to see only unique records??

    Or you want to see the duplicate records.

    Giving an example will make it easier to provide the required querty.

    BTW here is an simple query which is used to delete the duplicate records and keep the unique records in table.

    DELETE
    FROM table_name a
    WHERE EXISTS (SELECT 1
    FROM table_name b
    WHERE a.sv_id = b.sv_id
    AND a.sv_detail_id = b.sv_detail_id
    AND a.ROWID b.ROWID
    );Regards
    Arun

  • RELEVANCY SCORE 3.25

    DB:3.25:How To Identify A Primary Unique Key sa


    i have an existing table populated with data which currently has no constraints. i now need to decide which columns should be part of the primary unique key, is it just a simple case of building up a group query until the count is always 1 or is there a more sophisticated method.

    many thanks.

    DB:3.25:How To Identify A Primary Unique Key sa

    thanks again frank, i've had to shelve the problem for now......i will provide more info next time

  • RELEVANCY SCORE 3.24

    DB:3.24:Problem Inserting Records From Staging Table To Actual Table a9


    Hi all,

    I have 2 tables

    SAMPLE - ACTUAL RANGE-HASH PARTITIONED TABLE WITH UNIQUE INDEX ON COL1,COL2
    SAMPLE1 - STAGING TABLE TO LOAD WEEKLY DATA WITH SAME SET OF COLUMNS AS SAMPLE

    I am facing problem in inserting records to actual table even after removing duplicate entries from staging table.

    SELECT COUNT(*) FROM SAMPLE1 WHERE COL1 IN (SELECT COL1 FROM SAMPLE); -- 1047048
    DELETE FROM SAMPLE1 WHERE COL1 IN (SELECT COL1 FROM SAMPLE); -- 1047048
    COMMIT;
    INSERT INTO SAMPLE SELECT * FROM SAMPLE1;
    QL Error: ORA-00001: unique constraint (SCHEMA1.UQ_SAMPLE) violated
    00001. 00000 - "unique constraint (%s.%s) violated"
    SELECT COUNT(*) FROM SAMPLE1 WHERE COL1 IN (SELECT COL1 FROM SAMPLE); -- 0
    SELECT COUNT(*) FROM SAMPLE1 WHERE EXISTS (SELECT S1.COL1 FROM SAMPLE1 S1,SAMPLE S WHERE S1.COL1=S.COL1); -- 0

    MERGE INTO SAMPLE S
    USING
    ( SELECT * FROM SAMPLE1) S1
    ON (S.COL1 = S1.COL1 AND S.COL2=S1.COL2) WHEN NOT MATCHED THEN INSERT
    (COL1,COL2,COL3) VALUES
    (COL1,COL2,COL3);
    QL Error: ORA-00001: unique constraint (SCHEMA1.UQ_SAMPLE) violated
    00001. 00000 - "unique constraint (%s.%s) violated"Should I use anything font color="red"
    other than "IN" and "EXISTS" /font
    to cross check the records?

    Regards,
    Sam.

  • RELEVANCY SCORE 3.24

    DB:3.24:Unique Counts 3s


    I am using the SUMPRODUCT Formula with success.

    =SUMPRODUCT(--('RAW DATA'!$U$1:$U$13195=B4),--('RAW DATA'!$V$1:$V$13195="N"),--('RAW DATA'!$X$1:$X$13195="Y"))

    I would like to modify it such that if the formula above is true, count the unique values, in column A ('RAW DATA'!$A$1:$A$13195).

    Column A, could have many values repeated, so I would like to determine the number of unique values. Insight appreciated.

    DB:3.24:Unique Counts 3s

    Hi,

    You may refer to my solution at this link -
    http://www.ashishmathur.com/count-uniques-with-conditions/

    Hope this helps.

  • RELEVANCY SCORE 3.20

    DB:3.20:Xy Scatter Chart With Multiple Data Series k1


    I want to build a XY Scatter chart and have more than one data set. Each set of X and Y values are unique. So for 2 lines I will have 4 columns of data. Actually there is the X and Y columns in separate worksheets but in the same workbook. I have this
    working fine for one curve, but adding another data set creates problems and the Chart gets confused. How do I add the second set of X Y values to plot.

    DB:3.20:Xy Scatter Chart With Multiple Data Series k1

    Peter,
    The data was recorded over a period of 60 minutes.
    Thank you

  • RELEVANCY SCORE 3.20

    DB:3.20:Unique Key Values 7f


    Unique key columns accept null values. It is said that oracle treats every null value entered as unique from the other. How does it do it? Can somebody help on this please?

    DB:3.20:Unique Key Values 7f

    Hi Ram,

    Thnks for highlighting that issue.

    Didier:

    U have a composite Unique Constraint so try inserting

    insert into table values (null,null);
    insert into table values (null,null);

    Thsi will be accepted.

    That was the point of the discussion.

    Thanks once again Ram.

    Regards,
    Ganesh R

  • RELEVANCY SCORE 3.16

    DB:3.16:Select Count Distinct With 2 And More Columns c9



    Hi experts, i'm trying to get count of unique combination 2 or more colums from a table. For example from table sflight i would like to get count of unique combination of columns (CARRID CONNID). Is there any way? i have tried something like

    data count_comb type i.

    SELECT COUNT( DISTINCT carrid connid) INTO i FROM sflight.

    but it hasnt been compiled.

    Thanks for advice

    Moderator message : Not enough re-search before posting, discussion locked.

    Message was edited by: Vinod Kumar

    DB:3.16:Select Count Distinct With 2 And More Columns c9


    Hi Jorgen,

    The following code will give count of unique combination of columns

    DATA : no TYPE i .

    SELECT DISTINCT connid carrid INTO CORRESPONDING FIELDS OF TABLE it_spfli FROM spfli . DESCRIBE TABLE it_spfli LINES no .

    Thanks,

    Dhivya B

  • RELEVANCY SCORE 3.14

    DB:3.14:Unique Selection z7


    Post Author: SarkCA Forum: FormulaHi All, I hope that this is the right venue / forum topic to ask this. I am building a report for trending purposes and I have three Columns. Each column represents a year.I have the current year, previous year and two years ago. Under each year, I have two more columns. These represent financial periods. The difference between each period is exactly six months. My warehouses count twice a year, six months apart. We do not count in June or December. I have parameters that allow the user to select the year, count period and warehouse. In the previous year and two years ago, my data comes from my General Ledger as it is historical. My current year is unique. The data comes from a web form that populates database tables. The report looks something like this 2005 2006 2007 Period 1 Period 7 Period 1 Period 7 Period 1 Period 7InventoryWrite downSalesCost of sales The unique point here is that in the current year, the column under Period 1 can draw data from either the Inventory Tables if the current count period is 1 as selected by the parameter, or it can draw data from the general ledger if the current count period is 7. My problem is that the formula that I have created is not pulling in the historical general ledger data, for the correct period, when I select Period 7. My formula for the 2007, Period 1 column looks something like this: // For First Semi - Annual Count if{@FirstCountPd} = {?Current Period}and{@FirstCountPd} = {LOOKUP_TIME.PERIOD_NUMBER}and{@YearCurrent} = {LOOKUP_TIME.YEAR_NUMBER}then {inventory_table.inventory_balance}// For Second Semi - Annual Count else if {?Current Period} = {@SecondCountPd}and ({LOOKUP_TIME.PERIOD_NUMBER} -6) = {@PreviousPeriod}and {gl_inventory.YEAR_NUMBER} = {?Current Year}then{gl_inventory.base_amount} {@FirstCountPd} and {@PreviousPeriod} and {@SecondCountPd}are case formulas that select a period. For example, the first count period formula sets the count period to values that are between 1 and 5 regardless of which count period is selected. This is the formula elect {?Current Period} Case 1: 1 Case 2: 2 Case 3: 3 Case 4: 4 Case 5: 5 Case 7: 1 Case 8: 2 Case 9: 3 Case 10: 4 Case 11: 5 I have tried to pull in another alias of my gl_inventory.base_amount and set the period in that table to be 6 months less that the original gl_inventory.base_amount but the report returns no records.

    DB:3.14:Unique Selection z7

    Post Author: SarkCA Forum: FormulaHi All, I hope that this is the right venue / forum topic to ask this. I am building a report for trending purposes and I have three Columns. Each column represents a year.I have the current year, previous year and two years ago. Under each year, I have two more columns. These represent financial periods. The difference between each period is exactly six months. My warehouses count twice a year, six months apart. We do not count in June or December. I have parameters that allow the user to select the year, count period and warehouse. In the previous year and two years ago, my data comes from my General Ledger as it is historical. My current year is unique. The data comes from a web form that populates database tables. The report looks something like this 2005 2006 2007 Period 1 Period 7 Period 1 Period 7 Period 1 Period 7InventoryWrite downSalesCost of sales The unique point here is that in the current year, the column under Period 1 can draw data from either the Inventory Tables if the current count period is 1 as selected by the parameter, or it can draw data from the general ledger if the current count period is 7. My problem is that the formula that I have created is not pulling in the historical general ledger data, for the correct period, when I select Period 7. My formula for the 2007, Period 1 column looks something like this: // For First Semi - Annual Count if{@FirstCountPd} = {?Current Period}and{@FirstCountPd} = {LOOKUP_TIME.PERIOD_NUMBER}and{@YearCurrent} = {LOOKUP_TIME.YEAR_NUMBER}then {inventory_table.inventory_balance}// For Second Semi - Annual Count else if {?Current Period} = {@SecondCountPd}and ({LOOKUP_TIME.PERIOD_NUMBER} -6) = {@PreviousPeriod}and {gl_inventory.YEAR_NUMBER} = {?Current Year}then{gl_inventory.base_amount} {@FirstCountPd} and {@PreviousPeriod} and {@SecondCountPd}are case formulas that select a period. For example, the first count period formula sets the count period to values that are between 1 and 5 regardless of which count period is selected. This is the formula elect {?Current Period} Case 1: 1 Case 2: 2 Case 3: 3 Case 4: 4 Case 5: 5 Case 7: 1 Case 8: 2 Case 9: 3 Case 10: 4 Case 11: 5 I have tried to pull in another alias of my gl_inventory.base_amount and set the period in that table to be 6 months less that the original gl_inventory.base_amount but the report returns no records.

  • RELEVANCY SCORE 3.13

    DB:3.13:Count Distinct Values 1m


    How do i count distinct values in a query? I know how to do it in 2 queries, but if possible, I would like to do it in 1 query.

    Example:
    Data:
    City State
    San Francisco CA
    Tiburon CA
    Santa Rosa CA
    San Francisco CA
    Portland OR
    Portland OR
    Portland OR
    Eugene OR

    Desired Results:
    State Unique City Count
    CA 3
    OR 2

    Can this be done?

    DB:3.13:Count Distinct Values 1m

    I made the below changes and still get the same error. What am I doing wrong?

    SELECT [PHS Final OP Non Fixed Rates].Clm_NO, [PHS Final OP Non Fixed Rates].[Stanford Revenue Codes.Category],
    SUM([PHS Final OP Non Fixed Rates].[Amt_Allowed]) AS TotalDollars, CitiesCount.Srv_From_DetCount
    FROM [PHS Final OP Non Fixed Rates] INNER JOIN
    (SELECT Clm_NO, [PHS Final OP Non Fixed Rates].[Stanford Revenue Codes.Category], COUNT(*) AS Srv_From_DetCount
    FROM (SELECT DISTINCT Clm_NO, [PHS Final OP Non Fixed Rates].[Stanford Revenue Codes.Category],Srv_From_Det

    FROM [PHS Final OP Non Fixed Rates]) AS DistinctCities
    GROUP BY Clm_NO, [PHS Final OP Non Fixed Rates].[Stanford Revenue Codes.Category]) AS CitiesCount
    ON [PHS Final OP Non Fixed Rates].Clm_NO = CitiesCount.Clm_NO
    AND [PHS Final OP Non Fixed Rates].[Stanford Revenue Codes.Category] = CitiesCount.[Stanford Revenue Codes.Category]
    GROUP BY [PHS Final OP Non Fixed Rates].Clm_NO, [PHS Final OP Non Fixed Rates].[Stanford Revenue Codes.Category],CitiesCount.Srv_From_DetCount;

  • RELEVANCY SCORE 3.13

    DB:3.13:Excel To Db cx


    Hi,
    I have the excel files.which has few columns,no primary and unique key or values in files.
    some the data's are repeated like

    C1 C2
    Rajs usstechnology,raj@ymail.com
    rajs raj@ymail.com,usstechnology

    Most of the data shows like above.Actually both are correct.

    How to delete these type of datas while loading using sqlloader.

    DB:3.13:Excel To Db cx

    Hi,
    I have the excel files.which has few columns,no primary and unique key or values in files.
    some the data's are repeated like

    C1 C2
    Rajs usstechnology,raj@ymail.com
    rajs raj@ymail.com,usstechnology

    Most of the data shows like above.Actually both are correct.

    How to delete these type of datas while loading using sqlloader.

  • RELEVANCY SCORE 3.13

    DB:3.13:In The Report The Percentage Is Greater Than 100% kf


    Hi Guys I have drilldown report,this report has two queries Sumamry Data and Detailed Data.in the report I am inserting a New Column called Second Chance Email (%), for thismetric I amdividing these two columnswhich are
    Updated Email Count/Invalid EmailCount. According to requirement I am excluding InvalidNames from both the columns.
    The summary data ofSecond Chance Metriclooks good all the values in the summary data are less than 100% but in the detail data some values are more than 100%.
    I found thatthe values of Updated Email Count in the table (memeber table)i am pulling data are greater than the Invalid Email Count. The values in Update Email Count shsould not be more than Invalid Email Count. What must be going wrong.

    DB:3.13:In The Report The Percentage Is Greater Than 100% kf

    You probably are double counting rows because of a join (1 to many relationship), but we will need a query and sample data to verify.

    http://jahaines.blogspot.com/

  • RELEVANCY SCORE 3.12

    DB:3.12:Count Columns In A Row And Logic Relates 1a


    I need some help in oracle SQL,

    Student(unique) has five races in a table

    I need a select statement with student_id - 1 column

    I need to count the number columns with values 'Yes' in them

    the columns are race1,race2,race3,race4,race5 for a single row --- this should be 2 ndcolumn

    The 3rd column should contain the max of the race having 'yes' in them

    i.e if race1 and race 3 have values'yes' in them i need 3 in the next column, - 3rd column

    Can some one help me in this

  • RELEVANCY SCORE 3.12

    DB:3.12:Export Profile Results Into Excel??? Urgent sj



    HI,

    I WANT TO EXPORT PROFILING RESULTS IN TO EXCEL FILE. I CAN EXPORT THE DATA PATTERNS RESULT BUT NOT THE INFORMATION WHICH CONTAINS THE PROFILE RESULTS , WHICH HAS UNIQUE VALUES COUNT, NULL VALUES COUNT, INFERRED DATA TYPE, DOCUMENT DAT ATYPE.

    I WANT IT ON AN URGENT BASIS. PLS HELP///

  • RELEVANCY SCORE 3.11

    DB:3.11:Count Unique Occurance Of A Name 1m


    I have a list with 3 columns
    A B C D
    Name Class Date Payment
    JackMath 8/22/11Cash
    JackEnglish7/10/11 Charged
    Jack Gym7/11/11 Charged
    Bill Gym7/15/11Cash
    BillEnglish 8/1/11 Charged

    I need to be able to count the number of Unique Namesthat registered for a class between two dates and charged their fees.
    For example based on the above data - for Dates between 7/1/11 and 7/30/11 - how many Names Charged their fees.
    Answer should be 1. Jack

    if the dates were 7/1/11 to 8/15/11 the answer would be 2.

    Thanks for your help

    DB:3.11:Count Unique Occurance Of A Name 1m


    ... I'm trying to count the unique occurances[sic] of a name that is in Column H....

  • RELEVANCY SCORE 3.11

    DB:3.11:Percentange Calculation In Sql 77



    How can we calculate unique count percentage and normal percentage by a group?

    data test;

    input Q1 Q2 Q3 Q4 Q5 Age BU;

    cards;

    1 5 2 3 4 3 3

    4 5 2 1 2 1 3

    2 4 4 3 2 3 2

    1 3 2 5 3 2 3

    1 2 4 2 1 2 2;

    run;

    The following program calculates unique count and count for Q1 by Age.

    proc sql noprint;

    create table file1 as

    select Age,Count(Distinct Q1) as Unique, Count(Q2) as Count

    from test

    group by Age;

    quit;

    AgeUniqueCountUnique %%11125%20%21225%40%32250%40%

    I am unable to calculate percentage for these two columns. Refer the table above. The percentage is count for each case by total values. Is it possible via data step or PROC SQL?

    DB:3.11:Percentange Calculation In Sql 77


    This question has been asked many times before like: https://communities.sas.com/message/115464 , https://communities.sas.com/thread/30943

    If you are new to SAS my preference is leaving out any restriction learning all the possibilities with their advantages and disadvantages.As SAS is more as just a data-step or SQL language I made the reference to procedures, there are several of them.

    Reviewing the options with proc means it is remarkable what you can do with that. Base SAS(R) 9.4 Procedures Guide, Second Edition

  • RELEVANCY SCORE 3.10

    DB:3.10:Count Unique Values In Pivot Table f7


    I set up a pivot table to sumdata (e.g. hours) and also count data (e.g. the number of resources). Is there any way to count unique values since a resource can be listed multiple times if performing multiple roles on a project?

    DB:3.10:Count Unique Values In Pivot Table f7

    Hi,

    A Pivot seems to work well. Here's a screenshot.

  • RELEVANCY SCORE 3.09

    DB:3.09:How To Count No. Of Unique Values Only sa



    Suppose I have a dataset with 100 char and 100 numeric variables and I want to count the no. of unique observations in char variables only ...

    I know we could use the count distinct in Proc SQL but I wanted to know if there is any method using data step approach...

    Thank you,

    Rockerd.

    DB:3.09:How To Count No. Of Unique Values Only sa


    Thank you Data NULL for reminding me of these options. I believe that the LEVELS option is an alias for the NLEVELS option. Without a TABLE statement you not only get the summary shown by Data NULL, but also a summary for each level of each variable. Adding a TABLE statement yields only the overall summary for the selected variables. Since you want all variables try the following:

    Re: How to count no. of unique values only

    proc freq data=sashelp.class nlevels;

    table all / noprint;

    run;

    https://communities.sas.com/pre

  • RELEVANCY SCORE 3.09

    DB:3.09:Excel 2007 Pivot Tables Count Of Unique Values cc


    Using Excel 2007 Pivot Tables, is there a way to count the number of UNIQUE values. For Example, If i have two transactions on May 1, 5 transactions on May 3
    and 27 transactions on May 7, I would like to show a result of 3. Thanks in advance
    Marvin

    DB:3.09:Excel 2007 Pivot Tables Count Of Unique Values cc

    I found a workaround which helped me solve a similar problem I had.

    1. Insert a new column - let us call this "Date to Count" - in the source tab.
    2. Sort the values in the source tab by the Date field.
    3. Write a formula in the "Date to Count" column which reads "=IF(A1=A2,"",1)", assuming Column A contains the Date value.
    4. Copy it across the "Date to Count" column.
    5. For every change in Date this gives us the value of 1.
    6. In the pivot table, Sum the values in the "Date to Count" column.

    Hope this helps :)

    Ravi

  • RELEVANCY SCORE 3.09

    DB:3.09:Mv With Join - Performance Question 7z


    Hi. I have two tables: T1 automatically receives data from a source at about 50-100 records/hour and at any given time will contain around 200K records. T2 is a static table containing approx. 1.5 M. records.

    The layout of T1 is considerable ( 25 columns) so my MV focuses on the columns of interest. T2 contains only 5 columns.

    The query that will serve as the basis for my MV will be something like this:

    select a.c1, a.c2, a.c3, count(a.c2), b.c1, b.c2
    from t1 a, t2 b
    where a.c2 between 'b.c3' and 'b.c4'
    group by a.c1, a.c2, a.c3, b.c1, b.c2

    Obviously, the 'where' element of my statement causes lots of waiting. a.c2 is a number column. b.c3 and b.c4 are also number columns that have a range of values; for example, records would be like (record1) b.c3 '1' and b.c4 '100', (record2) b.c3 '101' b.c4 '200', c.

    I currently have no indexes on any of the columns and have a MV log monitoring T1. This MV would be set to refresh every hour. Oh, the db is 10g R2 on Win 2K3.

    So my questions are:
    (1) Is a MV the best approach to this query?
    (2) The docs on MVs tell me to index key columns. Would indexing the static table (T2) columns c3 and c4 (taking into account all records are unique) give me a performance boost?

    Thanks

    DB:3.09:Mv With Join - Performance Question 7z

    Try to concentrate on the query itself, and forget the fact that it's an MV, as this complicates unnecessarily the issue. How long is the query running for when you run it "standalone"? Your query is not standard, as you have a very unique "join" between T1 and T2, and therefore it's not obvious at all how the optimizer handles it. Show us the execution plan and we should be able to help more.

    Daniel

  • RELEVANCY SCORE 3.09

    DB:3.09:Count Unique Values 8c


    I have a linear range (one column, consecutive cells) set to the variable TheRng.
    I wish to count the number of unique values in that range using VBA.
    All the values are strings. How would I do this?
    Thanks for your time. Otto

    DB:3.09:Count Unique Values 8c

    Thanks Chip. That works for me. Otto

  • RELEVANCY SCORE 3.08

    DB:3.08:Need To Write An Expression To Find The Count 3c



    Hi All,

    I have 3 columns User Id, Name , Test Score with values ,but there are null values for Test score alone,

    I need to write an expression to find the count of User Id's with Test Score to find out the percentage of Users who have completed the tests

    Attached is the data sheet.

    Tried with the below expression but it failed..

    count(if not isnull(testscore), testscore))


    DB:3.08:Need To Write An Expression To Find The Count 3c


    try like:

    =count(if (len(trim([Test Score]))0, [Test Score]))

  • RELEVANCY SCORE 3.08

    DB:3.08:Count Unique Values Based Upon Critieria pj


    Hi I am trying to calculate the number of unique values in a range of data, however I am doing this based upon a set of criteria in some corresponding columns
    I can get the number of rows that match the criteria by using this formula (entered as an array)

    =SUM(IF('Source Data'!$B$15:$B$220=Infra,IF('Source Data'!$F$15:$F$220=New,IF('Source Data'!$T$15:$T$220=Y,1,0))))
    So it counts how many rows have “Infra” and “New” in two separate columns, this gives me 30 rows, however in a 3suprd/sup column I have a set of names and I want to identify how many unique values are contained within the 30 rows that match above, there
    are 22 unique names within the 30 rows. Can identify the unique values by using either of the formulas below :
    =SUM(IF('Source Data'!$G$15:$G$2200,1/COUNTIF('Source Data'!$G$15:$G$220,'Source Data'!$G$15:$G$220),0),0)))
    But I cant combine them, any ideas how I can combine or identify the 22 unique values
    Thanks

    DB:3.08:Count Unique Values Based Upon Critieria pj

    Here is a different kind of formula that should work in all versions of Excel.
    It's an array formula too, confirmed with CtrlShiftEnter:
    =SUM(IF(FREQUENCY(IF('Source Data'!$C$15:$C$220=Infra,IF('Source Data'!$F$15:$F$220=New,IF('Source Data'!$T$15:$T$220=Y,IF('Source Data'!$G$15:$G$220,MATCH('Source Data'!$G$15:$G$220,'Source Data'!$G$15:$G$220,0))))),ROW('Source Data'!$G$15:$G$220)-ROW('Source
    Data'!$G$15)1),1))Regards, Hans Vogelaar

  • RELEVANCY SCORE 3.07

    DB:3.07:Count Unique Values In A Column ds


    I have a column of city names. I want to create a list of unique city names and a count of that city name. For example:City NameLos AngelesNew York CityLos AngelesLos AngelesSeattleNew York CityMiamiDallasSeattleDallasDallasI would like a count of each unique city name.For example:City NameCountLos Angeles3New York City2Seattle2Miami1Dallas3Ideas?Thanks.

    DB:3.07:Count Unique Values In A Column ds

    Aaron,The following solution finds distinct city names, lists them and counts the frequency of ocurrance.The formula in column B of Data is:=IF(COUNTIF(OFFSET($A$1, 0,0, ROW()-1),A)=0, MAX(OFFSET($A$1, 0, 1, ROW()-1))+1, "")The formula in column A of Summary is:=IF(MAX(Data :: B)=ROW()-1, LOOKUP(ROW()-1, Data :: B, Data :: A), "")And, the formula in column B of Summary is:=COUNTIF(Data :: A, A) if you are in fact interested in the frequency.Hope this does it for you.You asked for unique names, but I believe that you mean to say distinct. The only unique name in your list is Miami, since it's the only one that appears only once, making it unique.Jerry

  • RELEVANCY SCORE 3.07

    DB:3.07:How To Handle One To Manys Case? These Columns Dont Currently Have Unique Values. 98



    These columns don't currently have unique values. Error atRelations

    DataSet ds = new DataSet();

    DataTable tableCopy = table.Copy();
    tableCopy.TableName = table_name;
    ds.Tables.Add(tableCopy);

    DataTable table2Copy = table2.Copy();
    table2Copy.TableName = table_name2;
    ds.Tables.Add(table2Copy);

    DataRelation relation;
    relation = new DataRelation(TableAAndTableB, ds.Tables[1].Columns[joinkey2], ds.Tables[0].Columns[joinkey1]);
    ds.Relations.Add(relation);

    select a.ProductID, COUNT(a.ProductID) from DBF_PRODUCT a group by a.ProductID
    check this is unique
    select b.Underlying, COUNT(b.Underlying) from DBF_PRODUCT_PNOTE b group by b.Underlying
    000001 CH 1
    000002 CH 2
    000063 CH 1
    000157 CH 1
    000402 CH 5

    DBF_PRODUCT_PNOTE's Underlying has multiple
    how to use above code to handle one to many's case?

    No need to worry as many books are not the core and true story

    DB:3.07:How To Handle One To Manys Case? These Columns Dont Currently Have Unique Values. 98

    I don't understand ... I thought that you said that the ProductID in your parent table (DBF_PRODUCT) was unique:

    select a.ProductID, COUNT(a.ProductID) from DBF_PRODUCT a group by a.ProductID

    check this is unique

    So, in your one-to-many relationship, the parent is DBF_PRODUCT, right? ProductID is unique. The column in your child table is the many part of the relationship ... is that the Underlying column in the DBF_PRODUCT_PNOTE table? Perhaps you're trying to
    set up the relationship with the wrong columns, but if I what I just stated is correct, then the code I posted earlier should work. Here it is again:
    relation = new DataRelation(ProductNote, ds.Tables[DBF_PRODUCT].Columns[ProductID], ds.Tables[DBF_PRODUCT_PNOTE].Columns[Underlying]);

    If the above isn't working, it's because you don't have unique values in ProductID, although I thought you said that you did.~~Bonnie Berent DeWitt [C# MVP]
    http://geek-goddess-bonnie.blogspot.com

  • RELEVANCY SCORE 3.05

    DB:3.05:Query Not Using Clustered Index m9


    Hello everybody,
    this is so weird.
    i have a table with 140,412,450 rows in it.
    there columns in it are indexed

    Group id -- has 2777 distinct values
    emp_id --- has 143,233 distinct values
    and a date column column service date which could be any date i could not see the distinct values in this.
    There is a clustered index on Groupid
    non clustered on emp_id and date column
    when iwanted to see the distinct values for all these column in the table
    i did
    select count(distinct group_id) from table 1(nolock)
    it uses the date index non clustered index scan in the execution plan
    when i run this

    select count(distinct emp_id) from table1 (nolock)
    it properly used the emp_id non clustered and did a non clustered index scan and did the calculations

    the data will be updated monthly. and expects to have about samenumber of rows in the table.
    its mostly read only
    is this correct to hve a clustered index on not a very unique column.. (group_id)

    there are queries which filteron group_id by other users.
    And i would be using another date column called paid date and i would be creating a non clustered index.

    but i am thinking of the clusteredindex..

    Please suggest

    DB:3.05:Query Not Using Clustered Index m9

    Ideally your clustered index will be on a unique value (if not sql will add a column to make it unique)
    Ideally it will also increment - to avoid fragemntation by insertign randomly into the data.
    You also don't want teh value to change - ie not effected
    by updates.
    There is a good video here on clustered indexes:
    http://technet.microsoft.com/en-us/sqlserver/gg508879.aspx

    Seth

    http://lqqsql.wordpress.com

  • RELEVANCY SCORE 3.05

    DB:3.05:Unique Index Violation When The Data Does Not Have Any Violation sj


    I have a table with a 7 column unique index

    I do select unique index columns,count(*) from queue_table group by columns having count(*) 1;

    I get no rows.

    When I try and insert into working_table which has the unique index in place, I get the error

    Edited by: amin_adatia on 3-Dec-2012 7:30 AM

    PLease ignore this ! Bad code on my part. Sorry.

  • RELEVANCY SCORE 3.05

    DB:3.05:Table Box Missing Rows zc



    I have what is essentially a bunch of general ledger data in QV11. My data model is pretty simple, one table with 20 or so columns.

    When I filter the data through the use of selection fields, my pivot table shows the correct sum(amount) over all the resulting accounts. I have a tab called DATA that I use in debugging that is just a Table Box with 10-15 columns of data. If I display a Text Object with count(amount) I get 478. Another Text Object with sum(amount) I get say $100,000.00. My pivot table show $100,000.00 in the title bar where I show sum(amount). However, if I export theTable Box to Excel or hand count the rows, the hand count and the count in Excel shows 298 records and $99,876.72. I can't seem to figure out why the table box is missing data. Is it only showing unique rows of the subset of columns that I selected? Actually I checked that by selecting all possible columns and I actually ended up with a few more rows and a different sum(amount).

    I checked the source data from the accounting system and it ties exactly to the sum(amount) and count(amount) values so I'm confused. Is there some filtering that the Table Box is doing?

    DB:3.05:Table Box Missing Rows zc


    You' re right, it only shows unique rows of the subset of columns you selected.

    This is often confusing people, but I think it's by design.

    You could use a record ID field in your source table to get around this issue, include this record ID in your table box.

    (If you don't have one yet, you can create a new field during load

    ...

    recno() as RecordID,

    ...

    )

    Or use a straight table chart and add count() and sum() expressions, so you're data will still show only one line per unique rows of columns you selected, but you get the information how many records do show this unique row (and the sum of amount if needed.

    Hope this helps,

    Stefan

  • RELEVANCY SCORE 3.04

    DB:3.04:How To Write Custom Code Basing On Two Rows m9


    Hi,
    I have done some custom code.Now i m stuck at one point where i m not getting out..
    Problem is like.I have two columns. i need to create a count if those two columns are having same values.
    I need to write a function to get the count please suggest how to write that function the count of same values for both columns..I need to return that count to a text box so that i can show on report.
    Sample data is like
    col1 col2
    231 231
    4443 4443
    8857 8857
    0 0
    332 876

    Count is four .I need to retrun this count to a text box ..please suggest a function so that i can return to the report...

  • RELEVANCY SCORE 3.04

    DB:3.04:Compare Floating Point Values In A Column 1f


    I have a column defined as NUMBER. The values are say 1.2345 and 1.23443. Visually i can say they are almost the same but if i do select count(distinct colA) from table it gives two values, which is perfectly right. What i want to do is say if they are less than 0.0001 then count them as same value.

    You could argue if i use ROUND or TRUNCATE to the 'n' decimal point but i see this as an issue even even to two decimal places. If the 3rd digit after the decimal is greater than 5 it would round the 2nd digit. If i use TRUNCATE to 'n' places then the last digit might be smaller than the previous value so again the same issue.

    However, my problem is how do i do if its in 2 or 3 different rows for the same column. If they are in two columns, i am able to do (COLA - COLB) and if the value is 0.00001 then ignore or filter from the query result.

    Example:

    ColA , ColB
    Variable1, 1.23453876
    Variable1, 1.2342736
    Variable1, 1.23420087
    Variable2, 10.250
    Variable2, 15.775
    Variable3, -1.93815994262695
    Variable3, -1.93815803527832

    Output: Count of unique values for each variable in columnA

    Variable1, 1
    Variable2, 2
    Variable3, 1

    OR

    Output: Display on those variables whose values is more than 0.0001 (4 digits after the decimal)

    Variable2, 2

    I am using oracle 10.2 Enterprise Edition and accessing data via TOAD

    Thanks

    DB:3.04:Compare Floating Point Values In A Column 1f

    Frank,

    good question. I apologize for not stating my intent for the query. I agree they are all distinct values. I wanted to narrow down the big deltas from small deltas.

    Example:
    Variable1, 1.23456
    Variable1, 1.234472
    Variable2, 10
    Variable2, 3
    Variable3, 5.1234
    Variable3, 5.12337

    The goal was to identify Variable2 from the above data set.

  • RELEVANCY SCORE 3.04

    DB:3.04:Is There A Functionformula In Excel To Count Unique Values km


    Is there a function/Formula in Excel to count Unique values in a column? I have a table of data when Column H has say site id and each site id has more than one row. I need to find the number of unique site ids in column H.

    DB:3.04:Is There A Functionformula In Excel To Count Unique Values km

    =SUM(1/COUNTIF(H1:H10,H1:H10))

    Hi Chip,

    You formula show error once not metthe entrire range of data rows. Like H1:H10, should all populated from row 1-10, but if there's blanks or succeeding blanks (reserved for future data) will results error. But this was not mentioned from the OP, however
    it is still not flexible formula.

    Regards,
    ~jaeson

  • RELEVANCY SCORE 3.03

    DB:3.03:Re: Need Plsql Code To Find Out The Column/Combination Of Columns From A Given Table Which Will Be Unique Values kz


    Hello,you mean a query like the following:Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionSELECT column_1 ,column_2FROM my_tableGROUP BY column_1 ,column_2HAVING COUNT(*) = 1 And you want to build the select dynamically?How do you want to return the output?REF Cursor, dbms_output, ...?RegardsMarcus

    DB:3.03:Re: Need Plsql Code To Find Out The Column/Combination Of Columns From A Given Table Which Will Be Unique Values kz

    If you are lucky and have some tables with single columns that are unique you can use the below query if you have fresh stats computed on 100% of the data. This may get you 80% of what you are looking for.
    SELECT col.table_name, col.column_name
    FROM dba_tab_columns col, dba_tables tab
    WHERE col.owner = tab.owner
    AND col.table_name = tab.table_name
    AND col.num_distinct = tab.num_rows
    AND col.num_nulls = 0
    AND tab.owner NOT IN
    ( 'SYS',
    'SYSTEM',
    'MDSYS',
    'DBSNMP',
    'WMSYS',
    'XDB',
    'APPQOSSYS',
    'OPSG',
    'ORDDATA',
    'ORDSYS',
    'OUTLN',
    'CTXSYS',
    'OE',
    'HR',
    'TOAD' )
    ORDER BY tab.table_name, col.column_name;

  • RELEVANCY SCORE 3.02

    DB:3.02:Finding Unique Values In Only One Column xs


    I am using Access 2007 and I have 10 columns of data.I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.I have used this SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_LoanFROM [SFR Rates All];But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.  I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.I know that I am missing something basic but well, I can't figure it out.ThanksBob

    DB:3.02:Finding Unique Values In Only One Column xs

    Hi Carol,
    If you are on SQL Server 2005 or above, you can use this:
    WITH NumberedRows
    AS (SELECT *, rn AS ROW_NUMBER() OVER (PARTITION BY Sorter
    ORDER BY SProgram)
    FROM YourTable)
    DELETE FROM NumberedRows
    WHERE rn = 2;
    This will keep the row with the alphabetically first value of
    SProgram, and in case of ties it will just pick one at random.
    Do try in a test environment first!
    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

  • RELEVANCY SCORE 3.02

    DB:3.02:Count Unique Values From Filtered Data 3d


    This formula works great for the whole data set, but how do I turn this into a subtotal formula in order to utilize filters and still get results?

    =SUM(IF(FREQUENCY(MATCH(S2:S1500,S2:S1500,0),MATCH(S2:S1500,S2:S1500,0))0,1))

    Thanks in advance!

    DB:3.02:Count Unique Values From Filtered Data 3d

    ...both responses work. Thanks much!

    Glad that they worked and thanks for the feedback, you might like to mark both responses as answer.

  • RELEVANCY SCORE 3.01

    DB:3.01:Count Frequency Of Matching Values In Three Columns ff


    Columns C, H K
    I need to count onlythe first instance of each unique matching combo.
    Column C has dates, H has names, K has an error type.

    On the same row, i need to reflect a count of the first time all three has the same data.

    Date(C)Name(H) Error(K) Count (O)
    6/9/2012 Amy Chubbs Ignorant Error 1
    6/9/2012 Amy Chubbs Ignorant Error 0
    6/9/2012 Amy Chubbs SmartError 1
    6/9/2012 Amy Chubbs Ignorant Error 0
    6/9/2012 Amy Chubbs SmartError 0
    6/9/2012 Amy Chubbs SmartError 0
    6/9/2012Pat Skinny Ignorant Error 1
    6/9/2012Pat Skinny Ignorant Error 0
    6/9/2012Pat Skinny Ignorant Error 0

    DB:3.01:Count Frequency Of Matching Values In Three Columns ff

    Hi,

    Try this in cell O2

    =1*(SUMPRODUCT((C$2:C2=C2)*(H$2:H2=H2)*(K$2:K2=K2))=1)

    Hope this helps.

  • RELEVANCY SCORE 3.01

    DB:3.01:Counter For Unique Values 93


    Hi Guys,
    Need suggestion on the below situation..
    I have a Characteristic..
    Field1
    100
    200
    300
    400
    100
    100

    Now i have to populate a count of the unique values for this field in BEx functionality.
    So another field to be created which will show the result in report for above data as 4.
    It should just take count of 1 even if there are duplicates whether 100 or any other value is shown as many times in data.

    Please suggest how to achieve this.

    Regards
    Zabi

    DB:3.01:Counter For Unique Values 93


    the suggestion by Ganesh Bothewill not have duplicates... at least not if you select the reference characteristic correctly

  • RELEVANCY SCORE 3.01

    DB:3.01:Compare Two Columns And Find Unique Values kc


    I have two columns and need to keep the unique values from BOTH columns.For example:Column 1abcColumn 2bcdDesired results:ad('a' and 'd' are unique to both column 1 and column 2)How on earth? Thanks,Kurt

    DB:3.01:Compare Two Columns And Find Unique Values kc

    Thanks Barry!This is a great solution. And it works with two columns that have a different number of rows, even when a row has the same value.This is a correct solution to my problem.

  • RELEVANCY SCORE 3.00

    DB:3.00:Count Unique Array Function c9



    I've been trying to this but I'm having trouble working out a formula in a single cell. I want to be able to select a range of cells with several unique values and get an array result which counts each of the unique values.
    For example, if I have data in a column that looks like this:
    a
    a
    a
    b
    b
    c

    There are 3 a's, 2 b's and 1 c.
    So I would want an array result that looks like this:{3,2,1}
    Is this possible?
    Any help greatly appreciated!

    Cheers
    Josh

    DB:3.00:Count Unique Array Function c9


    A hint on why not to use Morefunc you can find at the top of

    I didn't go to that page but I'm guessing that you've posted some of it in the ngs before. Let me guess what it says: don't use that add-in because it's not open source and it might not work from version to version?
    Ok, using that logic then we shouldn't be using Excel. It's not open source and some things don't work from version to version.
    Why stop at Excel? I don't think my operating system, WinXP, is open source.
    --
    Biff
    Microsoft Excel MVP

  • RELEVANCY SCORE 3.00

    DB:3.00:Count Unique Values Among Duplicates In A Table 38


    The formulas in "Count the number of unique values by using functions" work great as long as I only use them on a single column.
    My problem is that my dupliacte values apear in different columns and I want to know how many unique values I have in theentier table.
    For example:

    111-58
    987-52
    456-89

    658-82
    695-88
    111-58

    998-11
    658-82
    258-44

    This is a simplified table, mine is much larger and more complecated. I can not sote the data as numbers as I need them in this specific format. I also can't use the filter option (and it didn't really work for me, either).
    How can I count the number of unique values in this table (7)?

    Any ideas?

    Thanks!

    DB:3.00:Count Unique Values Among Duplicates In A Table 38

    Count the number of values with COUNTA() like this,

  • RELEVANCY SCORE 2.99

    DB:2.99:One Table Having Columns Then Another Table Allow To Access 7c


    Hi Friends,
    First of all Thank you for supporting.
    This is Nagendra, From India. I need a Query to Retrive Data From One Table.
    Coming to my requirement.
    In My DataBase Having 2 Tables. First Table Name MST_Doc(Mater Table), second Table User_Doc(User Table).
    Table Structure MST_Doc (DocCode,DocDesc) values (1,'Scanned Doc') ;
    MST_Doc (DocCode,DocDesc) values (2,'BuildingPlan'); MST_Doc (DocCode,DocDesc) values (3,'Electricity').....
    MST_Doc (DocCode,DocDesc) values (29,'Others'),
    in user table he can upload upto 28 doccodes (DocCode is Unique) is mandatory.
    Query : In Mater Table 28 Doccode. User update 28 Doccodes is Mandatory, If he is not Update 28 DocCode, then not allow to process to next level in procedure.
    i have a Query for some Idea, it will be some useful as well as same format i need.
    If Exists ( Here i need Code for Access the Mandatory Data Compare to Master Table (Ex: 28 DocCode is Mandetary, below 28 Docode data not Exists) )
    begin
    our statement Coming
    end
    Here I am Trying to In, Between, Count Commands, But it is Showing Wrong.
    Please Write Query Do not pass any Links.
    Please help to Write this Query, I hope you will Resolve as soon as possible.
    Thank You.

    DB:2.99:One Table Having Columns Then Another Table Allow To Access 7c

    can you show the DDL of your 2 tables with some sample data?
    the logic expressed in sql would be better

  • RELEVANCY SCORE 2.98

    DB:2.98:Row Aggrigation 33


    Currently I have a report that has the following columns: Month, Quarter, Sales Total, Invoice Count, and Average Sales Total. Because there are three months within one quarter then I get three rows of data for my Sales Total and Invoice Count columns. I've resolved this by using a pivot table and eliminating the External Unique ID. However, I have a column that is calculating the Average Sales Total and in the pivot table it doesn't calculate correctly anymore as a roll up column. Does anyone have any suggestions on how to accomplish the Average Sales Total in the pivot table?

    DB:2.98:Row Aggrigation 33

    Currently I have a report that has the following columns: Month, Quarter, Sales Total, Invoice Count, and Average Sales Total. Because there are three months within one quarter then I get three rows of data for my Sales Total and Invoice Count columns. I've resolved this by using a pivot table and eliminating the External Unique ID. However, I have a column that is calculating the Average Sales Total and in the pivot table it doesn't calculate correctly anymore as a roll up column. Does anyone have any suggestions on how to accomplish the Average Sales Total in the pivot table?

  • RELEVANCY SCORE 2.98

    DB:2.98:Count Distinct Value From Database k7


    how do i count distinct values from multiple columns in a table?

    DB:2.98:Count Distinct Value From Database k7

    Take your time...it's not my homework or anything... just trying to learn new things.

    VR,

    The little program here will answer your question, but beyond that I’m hoping that it will also show a few ways where you can
    do some things better, including how to know absolutely that the user has entered not only “a number”, but the correct type of number (an integer in this case).

    It will introduce you to using a different event that I suspect you’ve used before called the
    .Validating
    event, and also use an
    ErrorProvider
    control (there’s one in your toolbox, just grab it and add it to your form and it will show up in the components
    tray at the bottom).

    We’ll use that to do two things: First to make sure they’ve entered a valid integer type and, if they have, that one of the
    rows of your column named “Item1” has that value. After all it makes no sense to calculate it knowing full well it’ll return zero! ;-)

    Additionally though, let’s also give the user the ability to simply query the data to return the number of rows where the values
    are consecutive in ascending order (i.e., Item2 = Item1 1) without regard to any particular starting number for Item1.

    When they click the “Find Matches” button, depending on which of the two radio buttons they’ve set, a function is called. The
    function has an overload, so depending on the syntax used, it will use one of the two routines and return an integer indicating the number of matches found.

    Let’s have a look at it in action first then I’ll show the code:

    We’ll leave it to the default and just see how many ordered rows there are no matter the numbers themselves:

    Now let’s try the other method where they indicate what the value of Item1 is:

    We’ll give it something that can’t be converted to an integer:

    Then I’ll change it to an integer, but one where there are no matches for “Item1” in any rows:

    Finally now, I’ll give it a value where there are matches in “Item1” for the number entered:

    It’s not all that much code, but given this forum’s new narrow layout, I’ve decided to put it on a
    page
    of my website here because I have more control of it.

    Have a look and I hope that helps. :)
    Please call me Frank :)

  • RELEVANCY SCORE 2.98

    DB:2.98:Calculating Unique Values Based On Three Columns ca


    I have a large array of data, with multiple columns and I am trying to find all the unique records in one column which meet two other criteria.

    My data looks something like
    CriteriaA = OfficeX
    CriteriaB = StatusA
    ColumnOfficeColumnNameColumnStatus
    Can appearNeeduniquerecords fromCan
    Appear more than once
    more than oncethis column
    I have a forumla which finds unique values with two columns with onecriteria, but I need it to count two criteria.

    DB:2.98:Calculating Unique Values Based On Three Columns ca

    I know this is correct as little programming I do is correct, but it works
    Code triggers on the change of a pull down menu via this code

    Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = $C$8 Then
    Application.Run changeMacro
    End If
    End Sub

    and performs this:
    Sub changeMacro()

    Dim strSquadron As String
    Dim columnOffset As Long
    Dim strOverdueCount As Long
    Dim strCurrentName As String


    strSquadron = Range($C$8).Value

    columnOffset = 1

    Do While Sheets(TMAAA).Range(A columnOffset).Value strSquadron
    columnOffset = columnOffset 1
    Loop

    Do While Sheets(TMAAA).Range(A columnOffset).Value = strSquadron

    If IsEmpty(Sheets(TMAAA).Range(K columnOffset)) = True Then
    lngEmptyCell = lngEmptyCell 1
    columnOffset = columnOffset 1
    If lngEmptyCell 10 Then
    Exit Sub
    End If
    ElseIf Sheets(TMAAA).Range(K columnOffset).Value *OVDUE Then
    If Sheets(TMAAA).Range(K columnOffset).Value = *SCHED Then
    If Sheets(TMAAA).Range(B columnOffset).Value = strCurrentName Then
    columnOffset = columnOffset 1
    ElseIf Sheets(TMAAA).Range(B columnOffset).Value strCurrentName Then
    strCurrentName = Sheets(TMAAA).Range(B columnOffset).Value
    strOverdueCount = strOverdueCount 1
    columnOffset = columnOffset 1
    End If
    Else
    columnOffset = columnOffset 1
    End If
    ElseIf Sheets(TMAAA).Range(K columnOffset) = *OVDUE Then
    If Sheets(TMAAA).Range(B columnOffset) = strCurrentName Then
    columnOffset = columnOffset 1
    ElseIf Sheets(TMAAA).Range(B columnOffset) strCurrentName Then
    strCurrentName = Sheets(TMAAA).Range(B columnOffset).Value
    strOverdueCount = strOverdueCount 1
    columnOffset = columnOffset 1
    End If
    End If

    Loop

    Range($D$10).Value = strOverdueCount

    End Sub

  • RELEVANCY SCORE 2.98

    DB:2.98:Sql Statement For Composite Primary Key xz


    Hi,

    I need a SQL statement for finding the Distinct values for combination of 2 Columns( Composite Primary key). I dont want to use Data Dictionary views.

    Table A
    c1 c2
    1 1
    1 2
    2 3

    Now for the above table combination of c1 and c2 is unique.

    Please advice.

    Cheers
    Ramkannan.A

  • RELEVANCY SCORE 2.98

    DB:2.98:How To Count Unique Values In A File While Excluding Duplicates. 9s


    I am currently working on data analysis for an experiment in which participants detected targets of a variety of types. I need to quickly identify and count unique detections without digging through the massive .csv file to find them. I am uncertain of
    the structure of the formula required to find the count I desire.

  • RELEVANCY SCORE 2.97

    DB:2.97:Determine If Values In Each Category Have Been Keyed For Each Unique Id 11


    Is there a way to determine if at least one of each category has been keyed for each unique ID. I have attached a very small example worksheet of how the data is keyed (may be additional columns and categories added in the future, but these are the columns
    I am concerned with) and included a final result worksheet. Note: My data set is very large, and grows daily.

    https://skydrive.live.com/redir?resid=B63E163DA0C7639A!608authkey=!AEOaM0ZhNnDH1yc

    DB:2.97:Determine If Values In Each Category Have Been Keyed For Each Unique Id 11

    Hi,

    Try this

    1. Enter this formula in cell B2 and copy down

    =IF(SUMPRODUCT(COUNTIF(Data!$E$7:$E$57,INDIRECT("'Data'!"ADDRESS(MATCH(A2,Data!$A$1:$A$54,0),2)":"ADDRESS(LOOKUP(2,1/(Data!$A$7:$A$54='Final Results'!A2),ROW(Data!$A$7:$A$54)),2))))0,"Yes","No")

    2. Enter this formula in cell C2 and copy down

    =IF(SUMPRODUCT(COUNTIF(Data!$F$7:$F$10,INDIRECT("'Data'!"ADDRESS(MATCH($A2,Data!$A$1:$A$54,0),2)":"ADDRESS(LOOKUP(2,1/(Data!$A$7:$A$54='Final Results'!$A2),ROW(Data!$A$7:$A$54)),2))))0,"Yes","No")

    3. Enter this formula in cell D2 and copy down

    =IF(SUMPRODUCT(COUNTIF(Data!$G$7:$G$11,INDIRECT("'Data'!"ADDRESS(MATCH($A2,Data!$A$1:$A$54,0),2)":"ADDRESS(LOOKUP(2,1/(Data!$A$7:$A$54='Final Results'!$A2),ROW(Data!$A$7:$A$54)),2))))0,"Yes","No")

    Hope this helps.

  • RELEVANCY SCORE 2.97

    DB:2.97:Db Get And Select Count(*) f1



    I need to find out how many records of type X I have in the database.

    Since select count(*) is not supported , I was thinking of using something like

    SELECT count(distinct(XXX)) from YYY (xxx are unique values)

    Is it possible to assign the returned result to a variable and how.

    DB Get only seems to be able to get columns values.

    Thanks

    DB:2.97:Db Get And Select Count(*) f1


    try to create a view :

    create view nbRowfromYYYY

    as

    SELECT count(distinct(XXX))as NbRow from YYY

    then use the DB Read step (select NbRow from nbRowfromYYYY), then the DB Get step.

  • RELEVANCY SCORE 2.97

    DB:2.97:Distinct With Compound Key 3d


    Hi all,I have a compound key dimension with, say, unique id and data load date as the keys.  the unique id will appear on multiple load dates, you get the idea!  The unique id is the name attribute for members of the dimension.If i do a simple select for the count of rows by the unique ids, i get duplicate unique ids appearing, as should be expected since they're not strictly the same member.How do i tweak the query to count only distinct member names instead of distinct members?The current query is
    select

     

    [Measures].[Row Count] on columns,
    [Work Info].[Work Number].

    CHILDREN on rows
    from

     

    [Work Orders Cube]

    DB:2.97:Distinct With Compound Key 3d

    It sounds like you need to setup a distinct count measure using the unique_id column.http://geekswithblogs.net/darrengosbell - please mark correct answers

  • RELEVANCY SCORE 2.96

    DB:2.96:Problem With Lookup Warning. mx


    I am received the following warning on my lookup.[LKP - idDimCustomer for Issuer [7830]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE. The way that I constructed the SQL for the lookup originally would have returned duplicated keys but I changed it to guarantee the keys were unique.  When we use the term 'key' in this context we are talking about the 'joins' we make between the Available Input columns and the Available Lookup Columns on the 2nd tab of the component yes?I have proved that my keys are unique by using a group by clause on the 'key' columns and a COUNT() column and returning records HAVING count() 1.  Nothing is returned thereby proving the uniqueness of my key.  But I continue to receive this warning.So either I am not understanding the warning correctly OR there is a bug in SSIS.Assuming that my understanding is the problem can anyone help me out by explaining more precisely what this warning means?Just in case it might be a bug this is the version of IS designer I am using (BIDS)Microsoft SQL Server Integration Services DesignerVersion 9.00.3042.00against this version of SQL server9.00.3042.00 SP2 Enterprise Edition (64-bit)TIAThis is the SQL that I am using for the lookup ...select Convert(int,IntStoredAsCharInDB) as IntData, idDimCustomer from dimCustomer where FrontOfficeCustomerReference '-' and FrontOfficeCustomerReference is not nulland IsCurrentRecord = 1This is the SQL that I used to prove the uniqueness of my data
    select IntStoredAsCharInDB, idDimCustomer, COUNT(IntStoredAsCharInDB) from dimCustomer where IntStoredAsCharInDB '-' and IntStoredAsCharInDB is not nulland IsCurrentRecord = 1GROUP BY IntStoredAsCharInDB,idDimCustomerHAVING COUNT(IntStoredAsCharInDB) 1

     

     
    This returned no records.  I am using Convert(int,IntStoredAsCharInDB) as the field to join to.For good measure I also checked this
    select IntStoredAsCharInDB, idDimCustomer, COUNT(idDimCustomer) from dimCustomer where IntStoredAsCharInDB '-' and IntStoredAsCharInDB is not nulland IsCurrentRecord = 1GROUP BY IntStoredAsCharInDB,idDimCustomerHAVING COUNT(idDimCustomer) 1

     

     
    It also returned no records.

    DB:2.96:Problem With Lookup Warning. mx

    Thanks a lot and apologies for posting the confusing code.You got straight to the bottom of the issue which was my niave use of COUNT().  Adam your later two queries flushed out the offending duplicate.Cheers,
    In the future, use the same query as your source.  Just add COUNT(*) as a new column, and group by all of the others.  The group by should include the exact SELECT statements including any derived columns (like your CONVERT functions).Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

  • RELEVANCY SCORE 2.95

    DB:2.95:Combination Of List Columns As Unique Values- List Settings 18


    Hi,
    I'll find that there is option in list column settings as Enforce unique values, but its applicable for single column only. I need to make combinations of list columns as unique.
    Howmake two or more columns as unique in sharepoint 2010 list?
    Anandhan.S

    DB:2.95:Combination Of List Columns As Unique Values- List Settings 18

    also please take a look at my reply at
    http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/394a345f-a44b-421d-8e7f-1366d396216c

  • RELEVANCY SCORE 2.95

    DB:2.95:Re: Dup_Val_On_Index k1


    Hi Altaf,

    First step: Identify the table's columns which are defined to hold unique values...

    Regards,
    Guido

    DB:2.95:Re: Dup_Val_On_Index k1

    Arun has diagnosed your problem. Look carefully at your insert statement. You are selecting a row from tmp_proposal and then inserting that same row back into tmp_proposal. Since you change nothing in the row you select, it will always raise DUPVAL_ON_INDEX.

    Now, to solve the problem, you need to think about what you are trying to accomplish. Should there be two different tables used (i.e. INSERT INTO a SELECT FROM b), or do you really need an update statement?

    John

    Message was edited by:
    John Spencer
    Fixed the TOO_MANY_ROWS per Padders' comment.

  • RELEVANCY SCORE 2.95

    DB:2.95:How To Prevent Duplicate Values Showing In Parameters List ms


    In reporting services 2005 I'm creating a parameter which is based on a query.  I have a table with many rows and trying to use one of the columns as my parameter value options.  However there are only 6 unique values in te 900 rows of data... my parameter list is showing parameter for every row, and not limiting to the unique values only.How do I make sure that the parameters displayed are only the unique values within the column?

    DB:2.95:How To Prevent Duplicate Values Showing In Parameters List ms

    Thanks - That worked awesome... took me a while to adjust and work out for my data but I got it!

  • RELEVANCY SCORE 2.94

    DB:2.94:Re: How To Create A "Conditional" Unique Key? jk


    Rene
    it is not a trick it is a unique index which simply requires the unique combination of the values of its two columns.

    DB:2.94:Re: How To Create A "Conditional" Unique Key? jk

    well, the OP says he do not care about old dates, so there is no need to index dates. Your solution is easy, it is probably more performant than mine, but it does not allow to have duplicates values for old dates, so it is not the same result.

    the original message states:
    For old data a date column has a value for current data this columns is empty. yes, it does not states that for old data, a date column has an unique values, so your solution is maybe to restrictive

  • RELEVANCY SCORE 2.94

    DB:2.94:Jsl: Select Where With Variable For Column Name fp



    Hi I'm trying to use select where to count the number of rows which meet criteria in 2 columns using a for loop. In the beginning of my script (not shown) I use a dialog to let the user choose 2 columns and this ends up in a list (collist). I then use an associative array on each to determine the unique values per column. A second dialog allows the user to choose a subset of those unique values per column which ends up in another list (poschosen). The problem I'm struggling with is inserting the columns from collist into the select where function. I've tried a bunch of different attempts at eval, expr, eval expr, parse, etc. to try to insert them appropriately but I just can't seem to find the correct way. I'm hoping someone has advice.

    Here is my code adapted for the Sample Data: Children's Popularity. I'd like to replace the values (line 14) School and Goals to be collist[1] and collist[2] respectively. Thank you for your help in advance Elizabeth

    dattable=Open( "$SAMPLE_DATA/Children's Popularity.jmp" );
    poschosen={"Ridge","Sand","main","stop","Popular","Grades","stop"};
    stoppos={4,7}; //determine the position of the separator "stop"
    Test1pos=subtract(stoppos[1],1); //determine the # of school column values chosen
    Test2posStart=add(stoppos[1],1); //determine where in the list the Goals column values start
    collist={"School", "Goals"};
    sumpositives=0;
    For (i = 1, i = Test1pos, i++, //loop through the chosen school column values
    For(f=Test2posStart, fstoppos[2],f++, //loop through the chosen Goals column values
    dattableselect where(School==poschosen[i] Goals==Poschosen[f]);
    num_selected=nrows(dattableget selected rows); //count the rows selected
    sumpositives=sumpositives+num_selected; //add the current selection count to previous count
    print(sumpositives); //should be 56 in this example
    ); //end of inner for loop
    ); //end of outer for loop

    DB:2.94:Jsl: Select Where With Variable For Column Name fp


    Nevermind I just figured it out (of course right after I post the question, doh)... line 14 works when I do this:

    dattableselect where(as column(dattable,collist[1])==poschosen[i] as column(dattable,collist[2])==Poschosen[f]);

  • RELEVANCY SCORE 2.93

    DB:2.93:Count Unique Values Only x9


    dear all,
    how can i count unique value with subtotal list, like as under.
    Col_A
    1
    3
    1
    4
    3
    3 as i want count.

    DB:2.93:Count Unique Values Only x9

    http://office.microsoft.com/en-us/excel-help/count-unique-values-among-duplicates-HP010070481.aspx

  • RELEVANCY SCORE 2.92

    DB:2.92:Return Unique Values From A Row Of Data 1s


    Hi,

    I need to write a formula that will show me in a cell which unique values appear in the same row in the previous 5 columns...

    Example

    A
    B
    C
    D
    E
    F

    1
    TECH
    ONLINE
    TECH
    ONLINE
    TECH
    TECH,ONLINE

    2
    TECH
    TECH
    TECH
    TECH
    TECH
    TECH

    3
    TECH
    ONLINE
    TECH
    OFFLINE
    ONLINE
    TECH,ONLINE,OFFLINE

    I can concatenate the previous cells but this duplicates values, and I only need to know what unique values have appeared as per my column F desired end result.

    I have tried using MAX and a few other formula functions but i am not being very succesful!

    Any ideas please??

    Thanks,
    Louise

    DB:2.92:Return Unique Values From A Row Of Data 1s

    VBA solution,

    Type this code in a Module (Got it from Net written by Jindon)
    In your file, press Alt+F11, then VBA screen will open
    Go to Insert/Module/copy this code in right side blank white screen.

    Function ConcatUniq(ByRef rng As Range, _
    ByVal myJoin AsString) AsStringDim r As Range
    Static dic AsObjectIf dic IsNothingThen _
    Set dic = CreateObject("Scripting.Dictionary")
    For Each r In rng
    dic(r.Value) = Empty
    Next
    ConcatUniq = Join$(dic.keys, myJoin)
    dic.RemoveAll
    End Function

    It will creat a User Defined Function (UDF) which can be used as a formula as shown in this screen shot. Give this formula at F1 and then drag down.

    =concatuniq(A1:E1,", ")

  • RELEVANCY SCORE 2.92

    DB:2.92:Mdx Combine Multiple Sets With Different Criteria kx


    Hi everyone,
    I would like to combine each data set to a report.
    here is my query.
    ----Total BRIEF touchpoints
    with
    member [Measures].[count] as
    [Measures].[Ecare Touchpoints]
    select {[Measures].[count]} on columns,
    non empty {[Ecare Partner].[Partner Name].MEMBERS}
    ON ROWS
    FROM [PdrCombined]
    where ([Ecare Transaction Date].[ECare Transaction Dates].[Transaction Month].[201405],
    [Ecare Transaction Type].[Transaction Type].[1])

    ----Total # BRIEF unique touchpoints

    with
    member [Measures].[count] as
    [Measures].[Ecare Unique Touchpoints]
    select {[Measures].[count]} on columns,
    non empty {[Ecare Partner].[Partner Name].MEMBERS}
    ON ROWS
    FROM [PdrCombined]
    where ([Ecare Transaction Date].[ECare Transaction Dates].[Transaction Month].[201405],
    [Ecare Transaction Type].[Transaction Type].[1])

    ---Total count of unique Prescribers by NPI
    with
    member [Measures].[count] as
    [Measures].[Ecare Unique Touchpoints]
    select {[Measures].[count]} on columns,
    non empty {[Ecare Partner].[Partner Name].MEMBERS}
    ON ROWS
    FROM [PdrCombined]
    where ([Ecare Transaction Date].[ECare Transaction Dates].[Transaction Month].[201405],
    [Ecare Transaction Type].[Transaction Type].[1],
    {[Ecare Unique NPI].[NPI Status].[Verified],[Ecare Unique NPI].[NPI Status].[Not Verified]}
    )

    I want the result to be 3 different columns, each of them showing the result with its criteria.

    Anyone can help me figure it out?
    Thanks.

    DB:2.92:Mdx Combine Multiple Sets With Different Criteria kx

    Hi Xiaopapa,
    According to your description, you want to join the results of two MDX queries together, right? Currently, this functionality isn’t supported in Analysis Services MDX. However, there is a workaround for this requirement, for the detail information about
    it, please refer to the link below to see the blog.
    Joining the results of two MDX queries together
    Besides, if you are using SQL Server Reporting Services, you can use Lookup function to join the column in different dataset into one table.
    http://msdn.microsoft.com/en-IN/library/ee210531.aspx
    Regards,Charlie Liao
    TechNet Community Support

  • RELEVANCY SCORE 2.92

    DB:2.92:Exception In Commit 37


    I have unique index on 4 columns. when user saves the data in the page,
    I have put the commit in try-catch block. when there is a unique index violation, then error is logged in the jdev console.
    how can I throw an error in the UI, notifying the user to pick valid values ?
    thanks

    DB:2.92:Exception In Commit 37

    Hello,
    If you have written exception handler "When Others" then your exception will be handled in plsql it self and OAF will not come to know about unique constraint validation. Please check if plsql is handling exception.

    Abdul Wahid

  • RELEVANCY SCORE 2.92

    DB:2.92:Find And Drop Duplicate Rows mz



    I have 2462 rows by 13 columns. Some of these rows contain duplicate values in 12 of the 13 columns. The column that always contains unique text is called PK.SAMPLE. How can I find these rows of almost exact duplicates and remove them from the data set?

    Thanks,

    mgm

    DB:2.92:Find And Drop Duplicate Rows mz


    I wrote a script recently to check to make sure I wasn't adding duplicate rows to a data table. Maybe this will help.

    The script iterates through all the rows in the table (i counter) and each of my 9 columns (k counter) comparing the values to those in the last row. If it finds any rows that are equal, it will delete the last row.

    Hope that makes sense. You would have to modify it to look for any duplicates, but the script should still be pretty straightforward.

    There are issues displaying my script.

    Message was edited by: MJaworski

  • RELEVANCY SCORE 2.92

    DB:2.92:Count Of Unique Values From One Of The Column Of Datagrid 7f


    Hi Pals,
    I want to find the count of unique values from a datagridview, is this possible in VB.Net ?
    See below example;

    Now I want to get the unique values from Priority/Status column with their names and count.
    Any help would be greatly appreciated.
    Thanks,
    Srini

    DB:2.92:Count Of Unique Values From One Of The Column Of Datagrid 7f

    Excellent.... This is what I was asking for....
    Thanks you
    Kevininstructor

    Kevininstructor

    MCC

  • RELEVANCY SCORE 2.92

    DB:2.92:Hiding Data Label Of Values 0 On Column Chart (Report Builder 2.0) kf


    I have a column chart in Report Builder 2.0 that is built by doing a count() of items. Some of the columns have a value of zero. When I turn data labels on, the data label of 0 shows. Is there any way to hide the data labels for 0 values? I'd prefer it only label the columns that have values.

  • RELEVANCY SCORE 2.91

    DB:2.91:Data Generation And Lookup Tables With Known Values 9f


    Is there a way to exclude lookup (foreign key related) tables in a data generation plan? Our scenario in simplified form is as follows: assume we have two tables tblPrimary and tblLookup. tblLookup has fields LookupCode and Description wich have known values. We would like to populate tblPrimary which has a Foreign Key constraint relating it to tblLookup on LookupCode. Currently when we select tblPrimary in the data generation plan tblLookup is automatically selected and we are unable to exclude it. The problem is that there isn't any way that we can see to generate the correct matching LookupCode/Description combinations since the two columns generate data independently and since LookupCode is a unique primary key we can't use the RegularExpression generator to specify specific values. The only thing we've found that works so far is to set the insert record count on tblLookup to 0 and then NOTselect to clear the data prior to generating data. This seems to work but it is combersome to then delete records in tblPrimary any time you rerun the generation plan to ensure it's primary key doesn't result in constraint errors when the primary key column is not an identity field. Is there some other way to exclude tblLookup without removing the FK constraint on tblPrimary or taking the approach listed above?
    Thanks,
    -dotnetwiz

    DB:2.91:Data Generation And Lookup Tables With Known Values 9f

    I have to agree. VSTS DB Edition is extremely promising for being able to do true TDD, in conjunction with those application layers that need access to real data (ignoring the discussion of mock objects, for the moment). However, I don't think it's quite there yet. To echo many of the concerns of this thread, my chief problem is being able to write unit tests that combine data generation for core dynamic tables with static values for lookup tables. 
     
    Features Available Today (VSTS DB Rocks!)

    When building the database project, VSTS DB can drop and re-deploy the local instance of the database
    After re-deploying the local instance of the database, a post-deploy script can be automatically run to populate lookup tables with static values.
    Data Generation Plans let one automatically generate test data for selected tables
    Starting up a unit test project allows one to specify that a specific data generation plan is run before the tests
    Features Not Available Today (Cancels out some of the Rock factor)

    If a table selected for data generation has a foreign key relationship with a column in a lookup table, the DG Plan refuses to let one skip generation of the lookup table rows -- despite the fact that one may have already populated the lookup table with static values using a post-deployment script. It instead has to generate values for these tables.
    I am currently exploring some of the workarounds discussed in these forums to solve this problem, but it would be great if in a service pack or future relase, the data generation plans could just be made a little more intelligent, so it could play nice with pre-loaded static values.
     
    Thanks,
     
    Thomas
     

  • RELEVANCY SCORE 2.91

    DB:2.91:Count Unique Values Based On Another Column 11


    I need to count the uniqueNAMES where the TEXT contains a "2" or "II"
    NAMES and TEXT are the columns/named data.

    This is returning #VALUE
    =SUM(IF(FREQUENCY(IF((AM_PRT_PH="*1*"),MATCH(AMEND_PROT,AMEND_PROT,0)),""),ROW(AMEND_PROT)-MIN(ROW(AMEND_PROT))+1),1)

    Thanks!

    DB:2.91:Count Unique Values Based On Another Column 11

    Hi,

    try this array formula (Ctrl+Shift+Enter)

    =SUMPRODUCT(1*(FREQUENCY(IF(ISNUMBER(MATCH($B$4:$B$12,$B$16:$B$17,0)),MATCH($C$4:$C$12,$C$4:$C$12,0)),ROW($C$4:$C$12)-ROW($C$3))0))

    Versions are in range B4:B12
    Names are in range C4:C12
    B16 has 1 and b17 has I

    The above formula will work as long as there are no blanks in range B4:B12. It is also possible to take care of that scenario but have not done so just to contain the size of the formula.

    Hope this helps.

  • RELEVANCY SCORE 2.91

    DB:2.91:Count Unique Matches Between Two Columns df


    I need to count unique matches between two columns. In this instance, I need to count how many unique listingbased on the name in A and valuein B. My original list is about 900k rows so, anything that works well with large ranges would be appreciated.

    Thanx

    DB:2.91:Count Unique Matches Between Two Columns df

    Hi,

    With 900K rows of data, writing any formula would be absolutely futile. If you are using Excel 2010or higher version, then you can try to solve this with the Power Pivot (a free add-in from MS for Excel 2010 and higher versions).

    You may refer to my solution at this link -
    http://www.ashishmathur.com/count-uniques-with-conditions/

    Hope this helps.

  • RELEVANCY SCORE 2.90

    DB:2.90:Tricky Questions About Set Analysis am



    Hi,

    I have loaded two tables, Table A Table B. (Table A B are not directly linked)

    In table A, there are columns A1, A2, A_Year

    In table B, there are columns B1, B2, B3, B_Year

    And then I have one sheet, where I have added a multi box, with fields A1 A2.

    Then I created a bar chart, using A_Year as dimension, and in the expression field, I used set analysis to check whether B1=P(A1) and B2=P(A2)

    The syntax is like this: count({B1= P([A1]), B2= P([A2])} B3)

    The tricky part for me is that B1 has more values than A1, and B2 has more values than A2. So with the above expression, the overall count of B table is not correct. Is there anyway to capture all the rows in B, instead of dropping the unique values in table B?

    Thanks!

  • RELEVANCY SCORE 2.90

    DB:2.90:How To Flatten This Data aj


    How to flatten this data so that Ent_ID becomes unique in the table and eventID can be broken to various columns as In the
    2nd table? I don't think I can use PIVOT function as the values is over 20 plus millions for event_ID

  • RELEVANCY SCORE 2.90

    DB:2.90:Finding Common Values In Columns Of Data pk


    Using Excel 2007

    Assume I have 10 columns, each containing both unique and common alphnumeric data

    What I would like to be able to do is:

    Identify all the variables that are common to
    all 10 (or whatever number) of columns.

    Many thanks for your help.

    DB:2.90:Finding Common Values In Columns Of Data pk

    Thank you for all your help on this.

    I've tried this out and I can see it works but I'm experimenting with various other workarounds but you've been a great help in getting me to clarify my thinking and try out various options.

  • RELEVANCY SCORE 2.90

    DB:2.90:Multiple Count Returns On One Line xj


    Hi All

    SELECT tblS.lclmAssignTo,
    (SELECT COUNT(lclmAssignTo) FROM tblS WHERE [tblS.Complete]= Yes) AS [Completed Calls],
    (select Count(lclmAssignTo) FROM tblS WHERE [tblS.Complete]= No) AS [Active Calls]
    FROM tblS
    GROUP BY tblS.lclmAssignTo

    table is tblS
    Fields:
    ID thousands of unique IDs
    lclmAssignTo
    Complete

    Want three columns - Complete = Yes and Complete = No grouped by lclmAssignTo

    My sql is only returning the totals for Complete Yes or No, not grouping it by lclmAssignTo values. What am I doing wrong? Thank you!

    DB:2.90:Multiple Count Returns On One Line xj

    Oh, I have no idea if that is supported in Access or not. But, with that bit of information, try this:
    SELECT tblS.lclmAssignTo,
    (SELECT COUNT(lclmAssignTo) FROM tblS t2 WHERE [tblS.Complete]= Yes And t2.lclmAssignTo = t1.lclmAssignTo) AS [Completed Calls],
    (select Count(lclmAssignTo) FROM tblS t3 WHERE [tblS.Complete]= No And t3.lclmAssignTo = t1.lclmAssignTo) AS [Active Calls]
    FROM tblS t1
    GROUP BY tblS.lclmAssignTo

  • RELEVANCY SCORE 2.89

    DB:2.89:Unique Values For Multiple Columns - Infopath 2010 19


    Hi,
    We have two columns - product type(lookup) and product name in a custom list created in sharepoint 2010. While saving we require to validate whether the product type and product name combination is unique in the list. If not it should not save and message
    that already there is a data with the same values.
    How can we do this. Please advice.
    Thanks in advance
    Veena RVeena Raju

    DB:2.89:Unique Values For Multiple Columns - Infopath 2010 19

    John, i'm having the same problem and i've done exactly as you've mentioned but nothing seems to happen.

    Would really appreciate your input on this.

    Below are the screenshots of the rules.
    Rule 1
    https://docs.google.com/open?id=0B9mbLH9AjD33T251cFM0SW9PcTg
    Rule 2
    https://docs.google.com/open?id=0B9mbLH9AjD33WTZLQkpjWHFjcVU

    Thanks in advance
    AznanAznan

  • RELEVANCY SCORE 2.89

    DB:2.89:Excel 2010 Remove Duplicate S p7


    In microsoft Excel 2010 Proffesional Plus, when I am using the Data'Remove Duplicates' for a set of data a strange thing is happening. If their are two duplicate records , the pop up message shows '82 duplicate values found and removed, no unique values
    remain'. The no i.e final count of records is correct i.e actual minus 2 but pop up shows records deleted as 82. For any other count of duplicate records like 3 the message shows the correct count. Was wondering whats the issue with 2 duplicate records?

    DB:2.89:Excel 2010 Remove Duplicate S p7

    Does the issue occur with all files or a particular file?

    Try creating a test Excel file and then verify the results.

    If issue is with particular file, you may upload a sample file to SkyDrive for us to better assist you.
    Refer the link below to add files to Sky drive:
    http://social.technet.microsoft.com/Forums/en-US/w7itproui/thread/4fc10639-02db-4665-993a-08d865088d65

    Note: Files on SkyDrive will be available for public view and highly recommended not to upload any confidential information.

  • RELEVANCY SCORE 2.89

    DB:2.89:Different Filters To Data In 2 Different Columns xx


    Oracle BI 11g

    Hi,

    I have 1 fact table Sales (ID, Date, VersionID, Count) and 2 dimension table - Calendar (Date, Year, Month, Week, Day, ...), Versions (ID, Name).
    I've created Time Hierarchy on table Calendar, so I can use AGO function. I made new logical column "Count (1 month ago)". It works correctly.

    Now I need create Analysis with columns: Month, Count, Count (1 month ago). BUT! In column "Count" I need to see only Budget version values, and in column "Count (1 month ago)" I need to see only Actual version values.

    I try to use filter for column "Count" - "Version is equal to Budget", but it works for all Analysis, but not for column "Count".

    How can I solve this problem?

  • RELEVANCY SCORE 2.89

    DB:2.89:Compare Column In Two Datasets And Output Count 7z



    I am trying to compare two datasets data1 and data2.

    I have the column name in both "var"

    and would like to check if there any differences in those two columns in the two datasets

    and then count the number of differences

    I then would like to call this macro function and save the count to macro variable x

    Somehow this doesnt work any help is appreciated. Thanks!ee

    %macro CompareUniqueCols(data1, data2, var);

    %* check if all the unique values in a column var of data set 1 and data set 2 are same and outputs the number of differences;

    proc sql;

    create table temp1 as select distinct var from data1 order by var;

    run;

    proc sql;

    create table temp2 as select distinct var from data2 order by var;

    run;

    proc compare base = temp2 compare = temp1 outall outnoequal out = outcompare ;

    run;

    proc sql noprint;

    select count(*) into: noCompareObs from outcompare ;

    run;

    noCompareObs;

    %mend;

    %let x = %CompareUniqueCols(data1, data2, var);

    DB:2.89:Compare Column In Two Datasets And Output Count 7z


    You are confused about what a macro is. SAS macro language is a tool to generate code. In general you cannot treat a macro as if it was a subroutine function.

    Here is an example of how you might modify your macro to allow the user to specify the macro variable that you want it use to return the counts.

    %macro CompareUniqueCols(data1, data2, var, mvar=x);
    %local noCompareObs;
    ...
    %let mvar=noCompareObs;
    %mend;

    %let x=;
    %CompareUniqueCols(data1=today,date2=yesterday,var=CustomerID, mvar=X);

    %put Result = x ;

  • RELEVANCY SCORE 2.89

    DB:2.89:Creating A Combined Formula xx


    I am trying to count the number of cells in the column thatcontain a date (only those that have started have anything entered)which I can accomplish by using this formula =COUNTIFS(J2:J2248,"0") how ever, I need to only count the rows that have unique
    values in a second column which I can use this formula for =SUM(IF(FREQUENCY(MATCH(B2:B2248,B2:B2248,0),MATCH(B2:B2248,B2:B2248,0))0,1)).

    My question is how do I combine the formulas to count for both criteria at one time.

    The second column has text data that sometimes appears in more than one row so I need to be able to count the start dates for only unique second column data so as not to double count

    DB:2.89:Creating A Combined Formula xx

    As an array formula, confirmed with Ctrl+Shift+Enter:

    =SUM(IF(FREQUENCY(IF(J2:J22480,MATCH(B2:B2248,B2:B2248,0)),MATCH(B2:B2248,B2:B2248,0))0,1))

  • RELEVANCY SCORE 2.89

    DB:2.89:Counting Number Of Firms And Missing Values mk



    Hi I am trying to get a feel for my data and the number of unique observations

    I am wanting to
    count the number of firms (the variable is called cusip).count the number of firm names (the variable is called firms)

    Any help is appreciated

    DB:2.89:Counting Number Of Firms And Missing Values mk


    PROC FREQ and PROC UNIVARIATE are two very useful procedures when it comes to data exploration.

  • RELEVANCY SCORE 2.89

    DB:2.89:Sql Query For Duplicate Rows pf


    A very basic question -

    I have a table with three columns Col1, Col2 and Col3. Col1 and Col2 can have duplicate values, but Col3 will be unique.

    Col2 can have three possible values - A, B, C.

    I want to see those distinct values of Col1 (for which there are multiple rows in the table) AND (Col2=A). Also, need to know count of the result.

    Haven't used SQL much, any help would be much appreciated. Thanks.

  • RELEVANCY SCORE 2.89

    DB:2.89:Count Unique Occurances Of A Name c3


    i have a very large table of data that looks like the following
    A B C
    Build 1 Floor1 DeskC
    Build 1 Floor 1 DeskA
    Build 1 Floor 1 DeskC
    Build 1 Floor 2 DeskC
    Build 2 Floor 2 DeskB
    Build 2 Floor 2 DeskC
    What i need is a count of the unique occurance of theDesk on each combination of columns A B. The results would look like the following. I want a count of the differentdesk types are on the floor - not the number
    of each type
    Build 1 Floor 1 2
    Build 1 Floor 2 1
    Build 2 Floor2 2
    not sure if i can do this in a pivot table or if a formula or vb code is needed to tag the first record as being the unique record in the file and then run the pivot.
    Thanksjmk

    DB:2.89:Count Unique Occurances Of A Name c3

    Hi,

    Since there is a period of time without any respond, I will
    temporary mark the reply as answer to closed this thread.
    And if you come back to find it doesn't work for you, please reply to us and unmark the answer, this thread will be re-opened.Sincerely,
    Max Meng

    Come back and mark the replies as
    answers if they help and unmark them if they provide no help.

  • RELEVANCY SCORE 2.88

    DB:2.88:Need A Procedure For The Given Data k9


    Hi All ,

    I am having table1 like this ..........
    ARF_IDIRESS_IDLG_ID

    AA3SSSBBB83062110033
    AA4SSSBBB83062110033
    AA4SSSBBB83063110033
    AA5SSSLLL96292110032
    AA5SSSLLL96293110032
    Aq6wwwppp77288110031

    using columns AR,F_ID,IR,ES AND S_ID --I need to update another table2 data with LG_ID.

    Table2 is having the above all columns and addtional columns ...
    whenever I will search with the columns data of table1.....they will match with table2 data and need to update the LG_ID. And I need the count of updated values...like

    If LG_ID is updated with 110033 --------then I want count as 3
    If LG_ID is updated with 110032 --------then I want count as 2
    If LG_ID is updated with 110031 --------then I want count as 1

    I want an pl/sql procedure for this.....

    Thanks
    sai

    DB:2.88:Need A Procedure For The Given Data k9

    TABLE2 is like this ....

    id ARF_IDIRESS_IDLG_IDAmt

    11 AA4SSSBBB830624525142000
    12 AA4SSSBBB830634526534111
    13 AA5SSSLLL96292854675700
    14 AA5SSSLLL96293451270600

    I want to the updated rows in another table3 like

    LG_ID F_ID AR NO.OF ROWS UPDATED
    110033 4 AA 3
    110032 5 AA 2
    110031 5 AA 1

    hOPE THIS IS CLEAR......

    Edited by: user12045030 on Jul 27, 2010 3:16 AM

  • RELEVANCY SCORE 2.88

    DB:2.88:Count(*) Without Summarizing The Data 7a


    Hi. Is there an elegant way today of getting a count(*) based on 3 of my 4 columns but retaining the 4th column in my results? Without a join, where in, where exists, subselect etc?
    I have 4 columns a,b,c,d. I'd like to tag a count on each record of the total number of rows with identical values in colsa,b,c.

    DB:2.88:Count(*) Without Summarizing The Data 7a

    Yes, in SQL Server 2005 take a look at the window functions, e.g.

    select *, COUNT(*) over (partition by Col1, Col2, Col3) as Count from Table

  • RELEVANCY SCORE 2.88

    DB:2.88:Find Unique Values In A Row From 2 Different Columns 11


    I have some data that looks similar to this:
    Column1 Column2 Column3 Column4 Column5
    GoodData 123 GoodData ABC GoodData
    GoodData 123 GoodData BCA Gooddata
    GoodData 456 GoodData BCA Gooddata
    and so on....
    each Columns 2 and 4 have duplicate values. I need to each unique instance where both those columns are you unique though. So if Column2 says 123 and Column4 says ABC for 1 row, but another row Column2 says 123 and Column4
    says BCA I want both rows. Additionally I want both rows if Column4 is the same both rows but Column2 is different. Hopefully that makes sense. I have tried doing with sort -unique with group-object and multiple sort -unique pipes with no luck.
    I cant seem to figure out the trick to getting what I am looking for. Any ideas would be appreciated.

    Thanks

    Chris

    Chris Morgan

    DB:2.88:Find Unique Values In A Row From 2 Different Columns 11

    Sorry for the confusion. David has what I was looking for. Only I used sort -unique instead of group to give me the proper output I was looking for. This gave me unique instances of the combined columns 2 and 4.

    Thanks everyone. Chris Morgan

  • RELEVANCY SCORE 2.87

    DB:2.87:Count The Number Of Unique Values Using Indirect Function 7k


    I would like to use this
    methodto count the unique values on a column:
    =SUM(IF(FREQUENCY(IF(LEN('Input table'!A1:A47)0,MATCH('Input table'!A1:A47,'Input table'!A1:A47,0),""), IF(LEN('Input table'!A1:A47)0,MATCH('Input table'!A1:A47,'Input table'!A1:A47,0),""))0,1))

    And it works.

    But now I would like to use the indirect function to relate it to different columns. So I replace the range with this
    INDIRECT("'Input table'!"ADDRESS(1,COLUMN())":"ADDRESS(ROW(),COLUMN()))

    And the formula above does not work anymore... I am I doing something wrong?
    N

  • RELEVANCY SCORE 2.87

    DB:2.87:Unique Value Count In Pivot Table xd


    Is it possible to select multiple fiters (or a single fiter) in a pivot table and then have one of your data fields calculate how many unique vales there is from one of the columns in your original data?

    I've used the following "lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count", but it doesn't work with grouping.

    Any help would be great - let me know if anything needs to be clarified or more explanation is needed, not sure if I got all the terminology correct.

  • RELEVANCY SCORE 2.87

    DB:2.87:Bex Query: Count Unique Key Figure Values xp



    Hi

    I need a little help with a query. I need to count the number of unique key figure values. Se example;

    Data:

    Employee1 - 2

    Employee2 - 3

    Employee3 - 2

    Employee4 - 1

    Employee5 - 2

    Employee6 - 1

    Employee7 - 2

    Wanted output:

    1 - 2

    2 - 4

    3 - 1

    What kind of options do I have?

    Thanks

    Mike

  • RELEVANCY SCORE 2.87

    DB:2.87:Count Unique Values m8


    Hi this is what i have

    Column c Column G
    site cancelled x1
    site cancelled x1
    x2
    x3
    site cancelled x4

    what i want is a formula to count the unique values in column gthat aree site cancelled in column c
    so using the example above the result wouyld be 2 because there is 2 x1 and 1 x4

    DB:2.87:Count Unique Values m8

    Hi,

    You may refer to my solution at the following link -
    http://www.ashishmathur.com/count-uniques-with-conditions/

    Hope this helps.

  • RELEVANCY SCORE 2.87

    DB:2.87:Counting Unique Values 99


    Can anyone tell me how to count the number of unique values in a column. The cells contain a combination of numbers and letters

    Thanks

    DB:2.87:Counting Unique Values 99

    I *think* I have a partial understanding. You seem to be looking for the count of each additional instance of a value after its first instance.

    So, what values would you want for this data set?:
    A
    A
    B
    B
    B
    1
    3
    3
    3
    3
    C
    C
    2
    2
    2
    2

    Count of "duplicate" letters: 4?
    Count of "duplicate" numbers: 6?

    Hi Ron,
    From your above data, i want the below to be counted for numbers letters respectively.

    A
    Unique

    A
    Duplicate

    B
    Unique

    B
    Duplicate

    B
    Duplicate

    1
    Unique

    3
    Unique

    3
    Duplicate

    3
    Duplicate

    3
    Duplicate

    C
    Unique

    C
    Duplicate

    2
    Unique

    2
    Duplicate

    2
    Duplicate

    2
    Duplicate

    I hope this makes some sense.

    Warm regards Dharmesh

    Number of numbers or letters: =COUNTA(A1:A20)
    Number of unique numbers or letters: =SUMPRODUCT((A1:A20"")/COUNTIF(A1:A20,A1:A20""))
    Number of numbers: =COUNT(A1:A20)
    Number of unique numbers: =SUMPRODUCT((ISNUMBER(A1:A20))/COUNTIF(A1:A20,A1:A20""))
    Number of letters: =COUNTA(A1:A20)-COUNT(A1:A20)
    Number of unique letters: =SUMPRODUCT(--(A1:A20""),NOT(ISNUMBER(A1:A20))/COUNTIF(A1:A20,A1:A20""))
    From these formulas you can calculate the number of duplicates of different types by subtractions of the respective formulas above, like this:
    Number of duplicate numbers or letters: =COUNTA(A1:A20)-SUMPRODUCT((A1:A20"")/COUNTIF(A1:A20,A1:A20""))
    Number of duplicate numbers: =COUNT(A1:A20)-SUMPRODUCT((ISNUMBER(A1:A20))/COUNTIF(A1:A20,A1:A20""))
    Number of duplicate letters: =COUNTA(A1:A20)-COUNT(A1:A20)- SUMPRODUCT(--(A1:A20""),NOT(ISNUMBER(A1:A20))/COUNTIF(A1:A20,A1:A20""))
    Hope this helps / Lars-Åke

  • RELEVANCY SCORE 2.87

    DB:2.87:Urgent Expalantion Reqired About Foreign Keys m3


    we are doing data migration project and we have get data from different regions data in flat files, but we have problem below like:

    i am facing a problem to declare unique constraints, because in some files column should be in number data type and same file from some other region is varchar2 but these two columns are unique ,these two columns don't having the any duplicate values.

    can i take this column data type in varchar2?

    why because if i am taking number type it cant accept varchar2

    any suggestions.................

    Regards,
    sh

    DB:2.87:Urgent Expalantion Reqired About Foreign Keys m3

    Correct, I myself do not use any surrogate keys (if possible).
    ;)

  • RELEVANCY SCORE 2.87

    DB:2.87:Counting Unique Values mf


    How to display unique values in a Table Field from Unbound text box
    (If there is Hello, Hello, Hello It must count Hello as 1 not as 3)
    =Dcount [Table] [Field] (Table Name is DATA and the field name is Dealer), I searched this in many resources tried many variants but not worked...

    Please Help!

  • RELEVANCY SCORE 2.86

    DB:2.86:How To Show Unique Values In The Form For A Column That In Not Unique cx


    I have a table table1. that has two columns col1 and col2.
    now in the form I have taken only col1 in my data block.
    Since col1 is not a primary key, it contains a lot of duplicate values.
    I want to show only distinct values on the form.I dont want to create any new databse object.
    could you please help me?

  • RELEVANCY SCORE 2.86

    DB:2.86:How To Show Aggregated (Unique) List Column Item Values In A Ootb Chart Web Part sx


    Dear All,
    I would appreciate any help on the following topic.
    I have a simple custom list with 3 columns created. I connect the list web part with a Chart Web Part (The OOTBone in 2010) and it works fine. When I filter the list the grath is filtered as well.
    The problem is that I need to represent a count of unique values (based on one of the available text columns) in the chart per category. What happens instead is that all items in each column are taken as unique and no aggregationis donewhich
    spoils the whole graph idea.
    Is there a parameter settings in the Connection or in the Chart Web partthat should be used. If no, is there a simple (no coding) way to put this unique values count (per column) in additional columns and then to put it from there in the graph.
    So far I have not managed to find a way to take OOTB the aggregated categories (x axis) and depict them in a simple grapth...
    If no simple solution is available I would still appreciate a more complex one :).
    Thanks in advance,
    Kindest Regards
    TihoG

  • RELEVANCY SCORE 2.86

    DB:2.86:Data Structure Question 7f


    I have a table with 3 columns (String, String, int). The values in each column is not unique but the three values in every row is unique. I can make an object that contains all three data types and just add it to an ArrayList and work like that for every row.

    I was wondering if there's an alternative approach without making an extra object and just use multiple types of Collections to deal with this.

    DB:2.86:Data Structure Question 7f

    Well that put's my mind at ease...
    cheers

  • RELEVANCY SCORE 2.86

    DB:2.86:How To Get A Distinct Count Of Multiple Columns 98


    I've done this before and I'll be darned why I can't recall this.

    Table with 3 columns with the primary key defined in column a.

    Row a b c
    num.
    1 020 how abc
    2 030 why def
    3 010 not ghi
    4 040 how abc
    5 050 yes def
    6 060 why def

    So what I want is a count of the unique values for columns b and c as if b and c were actually one field.

    So the result of the query would be:
    Count = 4 unique rows.

    Hopefully I'm making some sort of sense. It's a bit much to ask about the number of occurrences of each duplicate value, I'm sure just getting count of the unique rows of the non-indexed rows will be sufficient. Since the query is meant to be universal for multiple columns of numeric and alphanumeric data and can be expanded for multiple columns, I am simply trying to get a basic skeleton that works for an "on the fly" analysis.

    DB:2.86:How To Get A Distinct Count Of Multiple Columns 98

    Thanks to all of you. I had the idea and just couldn't nail it down. You did it.
    All of you understood what I poorly communicated. My apologies for the lack of prep with a table, I was running patches on the system and couldn't dare interrupt it on my laptop and starting Oracle 11 while Micro$lop was patching all sorts of evil code. This is something I'm setting up on my own time so it's a work I'm going to try to be proud of. So there will be plenty of instrumentation and debugging code for timing.

    I'd thought of concatenating columns and was worried it wouldn't backport to older Oracle versions (work goes back to 8.1.7) with nulls and other goofy things like long raw fields or lob/clobs plus the other DBs that are at various sites. What I'm trying to do is improve on a crappy data analysis tool SAP provides to use with reporting unique columns. (Basis people could recognize this as DB05). I tried dragging out the SQL that their ABAP/4 language generates from an SQL trace but it was unusable and often dies on internal storage which on most sites is about 4GB per process. Needless to say, the program is wasteful and I'm trying to keep the work on the DB where it belongs and discourage cowboys from doing this in the DB on the fly and at the same time make it simpler for junior members to use while having a semblance of an audit trail.

    What I'm trying to do is work out a tool that checks all the crappy secondary indexes that were created by folks who seem to think in ways that are from an alien species not yet speculated about even with the strongest psychedelics. One of them actually decreases performance by a minimum factor of 5. It's not much when it's 5 seconds but when the response time averages about 3 hours beforehand, you can imagine what it was like afterward. In this particular case, the "logic" was that the index needed to be defined by the order of the fields of the select clause. Some myths never die. So it's not easy to work out which ones haven't been used in a long time, so it's down to finding which of the custom ones are actually bad in terms of selectivity. Some of these may have made a little bit of sense when the system was started up, now that some of these have millions of rows instead of a few thousand, thaw tend to expose the flaws in design.

    I'm also trying to make this work on multiple DBs such as DB2, MaxDB and (sob) SQL Squealer. The idea is to write this in ABAP code as a utility without the overhead of internal tables in ABAP as some of these tables are many millions of rows and I have to account for the size in memory. (ABAP field sizes in internal memory are generally much larger than the actual DB lengths. so every bit helps!) So I'm trying to create actual SQL on the fly and use it internally without going through the SAP DB interface which limits the type of SQL functions used and all the overhead. It's intended to grab a secondary index and if there are more than 2 fields defined, work it backwards to show unique values for all columns, all columns - 1, all columns - 2 and so on back to the initial column in the index.

    Why does this seem important? On Oracle I can generate histograms but those can often screw up SAP. On other DBs, the indexing cardinality is much more important, particularly with DB optimizers that make some odd decisions. So I need evidence I can push to folks who will only understand if there's a plain and simple analysis that explains it (almost) in crayon.

    I'm testing null values next (which worked!) but these suggestions all seem to follow the same idea which won't work in ABAP without some serious memory issues. So I'm down to this type of coding:

    exec sql.
    dynamically generated true SQL
    end-exec.

    rather than using ABAP with their concept of "open SQL" and the DB interface with all the overhead.

    RB's suggestion of the UNION ALL wasn't quite what I was looking for BUT it made me think that this would be a nice enhancement for evaluations!

    So the candidates that seem to work for what I wanted are as follows:

    SQL SELECT COUNT (MIN (0)) AS distinct_col3_col4_cnt
    2 from rb group by b, c;

    DISTINCT_COL3_COL4_CNT
    ----------------------
    7
    select count(distinct(b || c)) num_rows from rb;

    So thanks also to Frank, APNL and Nimesh for their examples. Now the fun starts as I spec out the processing.

  • RELEVANCY SCORE 2.86

    DB:2.86:Count Of Data In A Particular Quarter 97



    Hi,

    I need to calculate the count of glucose values with dates in the Q1, then Q2,Q3,Q4.

    The columns in the table to be used are:

    Glucose Date

    I think I might need something like.

    count(if([Date]=Jan and [Date]=April,Glucose))

    Please advise!

    Thanks in Advance!

    DB:2.86:Count Of Data In A Particular Quarter 97


    Hi,

    Use like this

    count(if(Month([Date])=1and Month([Date])=4,Glucose))

    Regards,

    Iyyappan

  • RELEVANCY SCORE 2.86

    DB:2.86:[8i] Count Distinct Items, When A Unique Item Is Identified By 2 Columns? fz


    First, I have a query that gives me the results I want. I just want to know if this is the best way to get my count or if there's a better way...

    Here's my sample data:
    CREATE TABLEsteps
    (item_idCHAR(25)
    ,ord_nbrCHAR(10)
    ,sub_nbrCHAR(3)
    ,step_nbrCHAR(4)
    );

    INSERT INTO steps
    VALUES ('A','0000000001','001','0010');
    INSERT INTO steps
    VALUES ('A','0000000001','001','0020');
    INSERT INTO steps
    VALUES ('A','0000000001','001','0030');
    INSERT INTO steps
    VALUES ('A','0000000001','002','0010');
    INSERT INTO steps
    VALUES ('A','0000000001','002','0020');
    INSERT INTO steps
    VALUES ('A','0000000001','002','0030');
    INSERT INTO steps
    VALUES ('B','0000000002','001','0005');
    INSERT INTO steps
    VALUES ('B','0000000002','001','0025');What I'm trying to do is get a count of the unique orders (key: ord_nbr + sub_nbr) by item_id, as an analytical function (so, it will fill in for every row). Here is the query I have right now to do that:

    SELECTitem_id
    ,ord_nbr
    ,sub_nbr
    ,step_nbr
    ,COUNT(DISTINCT ord_nbr || sub_nbr)OVER( PARTITION BYitem_id )AS ord_cnt
    FROMsteps
    ;

    Results:
    ITEM_ID ORD_NBR SUB STEP ORD_CNT
    ------------------------- ---------- --- ---- -----------
    A 0000000001 001 0010 2.000
    A 0000000001 001 0020 2.000
    A 0000000001 001 0030 2.000
    A 0000000001 002 0010 2.000
    A 0000000001 002 0030 2.000
    A 0000000001 002 0020 2.000
    B 0000000002 001 0005 1.000
    B 0000000002 001 0025 1.000Is there a better way to get this count?

    Thanks!!

    DB:2.86:[8i] Count Distinct Items, When A Unique Item Is Identified By 2 Columns? fz

    I didn't make the database I'm working with, and I have no control over its structure. Technically, an order number does not have to be formatted as a number, it could be all letters, that just usually doesn't happen.

    I just thought that performing a function on a concatenated statement is generally slower than running it on a single column's value, so I was hoping there was a way to avoid that, as I'm dealing with a large dataset and don't want the speed of the query compromised if at all possible.

  • RELEVANCY SCORE 2.86

    DB:2.86:Count Unique Values In A Range In Excel 2010 jx


    Hello
    XP SP 3, Excel 2010.

    DB:2.86:Count Unique Values In A Range In Excel 2010 jx

    Thank you for your kind words. It is always refreshing to hear that my posts are continuing to help others.