DISTINCT ROW IN GRIDVIEW

I want to retrieve rows of data showing for each employee one value for Amount based on the criteria set.
So as in the example, only one record for emp_id 12894 or 15950 should be shown.

However, when I run the query I obtain individual entries for the specific criteria but not one row that sums the unit charged or amount field.

I am not using any vb code. My sql statement in the querybuilder is not generating unique records by employee id and as such my gridview does not display what I need.

See attached

Any help will be appreciated.
SCREENSHOT.doc
ernie_shahAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
Can you try this?
SELECT softech.GLTABLE_ARC.EMPL_ID, 
       softech.EMPLOYEE.FIRST_NAME, 
       softech.EMPLOYEE.LAST_NAME, 
       softech.GLTABLE_ARC.ACCOUNT_CODE, 
       SUM(softech.GLTABLE_ARC.AMOUNT)                AS AMOUNT, 
       softech.GLTABLE_ARC.SEGMENT1, 
       SUM(softech.GLTABLE_ARC.UNITS_CHARGED)         AS UNITS_CHARGED, 
       SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) AS LASTSEVENAC 
  FROM softech.EMPLOYEE, 
       softech.GLTABLE_ARC 
 WHERE softech.EMPLOYEE.EMPL_ID = softech.GLTABLE_ARC.EMPL_ID 
   AND softech.GLTABLE_ARC.CODE_FIELD IN( '771', '773', '779', '780' ) 
   AND SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' 
   AND softech.GLTABLE_ARC.PAY_YEAR = 2013 
   AND ( ( softech.GLTABLE_ARC.CYCLE_NO = 4 
           AND softech.GLTABLE_ARC.OOC_RUN >= TO_DATE('4/16/2013', 'MM/DD/YYYY') ) 
          OR ( softech.GLTABLE_ARC.CYCLE_NO = 4 
               AND softech.GLTABLE_ARC.OOC_RUN IS NULL ) 
          OR ( softech.GLTABLE_ARC.CYCLE_NO = 5 
               AND softech.GLTABLE_ARC.OOC_RUN <= TO_DATE('5/15/2013', 'MM/DD/YYYY') ) ) 
 GROUP BY --softech.GLTABLE_ARC.CYCLE_NO,   
--softech.GLTABLE_ARC.PAY_YEAR,   
--softech.GLTABLE_ARC.OOC_RUN,   
softech.GLTABLE_ARC.EMPL_ID, 
softech.EMPLOYEE.FIRST_NAME, 
softech.EMPLOYEE.LAST_NAME, 
--softech.GLTABLE_ARC.CODE_TYPE,   
--softech.GLTABLE_ARC.CODE_FIELD,   
softech.GLTABLE_ARC.ACCOUNT_CODE, 
softech.GLTABLE_ARC.SEGMENT1, 
SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) 
 ORDER BY softech.GLTABLE_ARC.EMPL_ID 

Open in new window

0
 
SharathData EngineerCommented:
Then remove the unwanted columns from GROUP BY clause.
SELECT softech.GLTABLE_ARC.EMPL_ID, 
       softech.EMPLOYEE.FIRST_NAME, 
       softech.EMPLOYEE.LAST_NAME, 
       softech.GLTABLE_ARC.ACCOUNT_CODE, 
       SUM(softech.GLTABLE_ARC.AMOUNT)                AS AMOUNT, 
       softech.GLTABLE_ARC.SEGMENT1, 
       SUM(softech.GLTABLE_ARC.UNITS_CHARGED)         AS UNITS_CHARGED, 
       SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) AS LASTSEVENAC 
  FROM softech.EMPLOYEE, 
       softech.GLTABLE_ARC 
 WHERE softech.EMPLOYEE.EMPL_ID = softech.GLTABLE_ARC.EMPL_ID 
 GROUP BY --softech.GLTABLE_ARC.CYCLE_NO, 
          --softech.GLTABLE_ARC.PAY_YEAR, 
          --softech.GLTABLE_ARC.OOC_RUN, 
          softech.GLTABLE_ARC.EMPL_ID, 
          softech.EMPLOYEE.FIRST_NAME, 
          softech.EMPLOYEE.LAST_NAME, 
          --softech.GLTABLE_ARC.CODE_TYPE, 
          --softech.GLTABLE_ARC.CODE_FIELD, 
          softech.GLTABLE_ARC.ACCOUNT_CODE, 
          softech.GLTABLE_ARC.SEGMENT1, 
          SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) 
HAVING ( softech.GLTABLE_ARC.CYCLE_NO = 4 ) 
       AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 ) 
       AND ( softech.GLTABLE_ARC.OOC_RUN >= TO_DATE('4/16/2013', 'MM/DD/YYYY') ) 
       AND ( softech.GLTABLE_ARC.CODE_FIELD = '771' 
              OR softech.GLTABLE_ARC.CODE_FIELD = '773' 
              OR softech.GLTABLE_ARC.CODE_FIELD = '779' 
              OR softech.GLTABLE_ARC.CODE_FIELD = '780' ) 
       AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' ) 
        OR AND (softech.GLTABLE_ARC.CYCLE_NO = 4) 
           AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 ) 
           AND ( softech.GLTABLE_ARC.OOC_RUN IS NULL ) 
           AND ( softech.GLTABLE_ARC.CODE_FIELD = '771' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '773' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '779' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '780' ) 
           AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' ) 
           AND ( softech.GLTABLE_ARC.CYCLE_NO = 5 ) 
           AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 ) 
           AND ( softech.GLTABLE_ARC.OOC_RUN <= TO_DATE('5/15/2013', 'MM/DD/YYYY') ) 
           AND ( softech.GLTABLE_ARC.CODE_FIELD = '771' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '773' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '779' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '780' ) 
           AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' ) 
 ORDER BY softech.GLTABLE_ARC.EMPL_ID 

Open in new window

0
 
ernie_shahAuthor Commented:
This does not work. No results are generated using the AND rather than the OR at line 31 and line 39.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
SharathData EngineerCommented:
First, there are lot of syntactical errors in your query.

Second, I have not changed anything except commenting unwanted columns in GROUP BY clause.

If that does not work, you have issues in your original query.
0
 
ernie_shahAuthor Commented:
I don't understand. The query below works when I use OR at line 31 and 39.
The problem is that I am not achieving the desired result.
Instead of being able to view one record per employee that shows the sum of amount,
I am seeing individual transactions for the employee.

SELECT softech.GLTABLE_ARC.EMPL_ID,
       softech.EMPLOYEE.FIRST_NAME,
       softech.EMPLOYEE.LAST_NAME,
       softech.GLTABLE_ARC.ACCOUNT_CODE,
       SUM(softech.GLTABLE_ARC.AMOUNT)                AS AMOUNT,
       softech.GLTABLE_ARC.SEGMENT1,
       SUM(softech.GLTABLE_ARC.UNITS_CHARGED)         AS UNITS_CHARGED,
       SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) AS LASTSEVENAC
  FROM softech.EMPLOYEE,
       softech.GLTABLE_ARC
 WHERE softech.EMPLOYEE.EMPL_ID = softech.GLTABLE_ARC.EMPL_ID
 GROUP BY --softech.GLTABLE_ARC.CYCLE_NO,
          --softech.GLTABLE_ARC.PAY_YEAR,
          --softech.GLTABLE_ARC.OOC_RUN,
          softech.GLTABLE_ARC.EMPL_ID,
          softech.EMPLOYEE.FIRST_NAME,
          softech.EMPLOYEE.LAST_NAME,
          --softech.GLTABLE_ARC.CODE_TYPE,
          --softech.GLTABLE_ARC.CODE_FIELD,
          softech.GLTABLE_ARC.ACCOUNT_CODE,
          softech.GLTABLE_ARC.SEGMENT1,
          SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2)
HAVING ( softech.GLTABLE_ARC.CYCLE_NO = 4 )
       AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 )
       AND ( softech.GLTABLE_ARC.OOC_RUN >= TO_DATE('4/16/2013', 'MM/DD/YYYY') )
       AND ( softech.GLTABLE_ARC.CODE_FIELD = '771'
              OR softech.GLTABLE_ARC.CODE_FIELD = '773'
              OR softech.GLTABLE_ARC.CODE_FIELD = '779'
              OR softech.GLTABLE_ARC.CODE_FIELD = '780' )
       AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' )
        OR  (softech.GLTABLE_ARC.CYCLE_NO = 4)
           AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 )
           AND ( softech.GLTABLE_ARC.OOC_RUN IS NULL )
           AND ( softech.GLTABLE_ARC.CODE_FIELD = '771'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '773'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '779'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '780' )
           AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' )
           OR ( softech.GLTABLE_ARC.CYCLE_NO = 5 )
           AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 )
           AND ( softech.GLTABLE_ARC.OOC_RUN <= TO_DATE('5/15/2013', 'MM/DD/YYYY') )
           AND ( softech.GLTABLE_ARC.CODE_FIELD = '771'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '773'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '779'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '780' )
           AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' )
 ORDER BY softech.GLTABLE_ARC.EMPL_ID  


Thanks
0
 
ernie_shahAuthor Commented:
Ok, I will try this in my query builder and get back to you. Off work till Monday so you will hear from me then. Thanks so much for your time and assistance. Hear from me soon.
0
 
ernie_shahAuthor Commented:
Good Morning:

My issue has been resolved.

Thanks alot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.