Solved

Showing COUNT of times an entry appears in a SELECT query (SQL query help)

Posted on 2014-10-06
16
239 Views
Last Modified: 2014-10-08
I have 2 queries...

select DISTINCT s.SUPPLIER_CODE AS SupplierCode from MEDMGR.CMTRS_TB_VALIDATED_SUPPLIERS s, MEDMGR.CMTRS_TO_BE_VALIDATED v, MEDMGR.CMTR_VALIDATION_SUPPLIER_LIST l where s.CMTR_ELID=v.CMTR_ELID and RECORD_DATE > 1/1/2014 and l.SUPPLIER_CODE= s.SUPPLIER_CODE and l.QUALITY_INDEX != -1

Open in new window


and

select COUNT(s.SUPPLIER_CODE) AS SupplierCodeCn from MEDMGR.CMTRS_TB_VALIDATED_SUPPLIERS s, MEDMGR.CMTRS_TO_BE_VALIDATED v, MEDMGR.CMTR_VALIDATION_SUPPLIER_LIST l where s.CMTR_ELID=v.CMTR_ELID and RECORD_DATE > 1/1/2014 and l.SUPPLIER_CODE= s.SUPPLIER_CODE and l.QUALITY_INDEX != -1

Open in new window


As you can see they are essentially the same query. 1 pulls the count and one pulls the distinct name of the count. How can I combine this query to show the distinct SUPPLIER_CODE and the number of times it appears in the result set?
0
Comment
Question by:Mike Miller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +2
16 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40364408
SELECT  s.supplier_code AS suppliercode, count(*) AS suppliercodecn
  FROM medmgr.cmtrs_tb_validated_suppliers s,
       medmgr.cmtrs_to_be_validated v,
       medmgr.cmtr_validation_supplier_list l
 WHERE s.cmtr_elid = v.cmtr_elid
   AND record_date > 1 / 1 / 2014
   AND l.supplier_code = s.supplier_code
   AND l.quality_index != -1
   group by s.supplier_code
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40364416
Maybe I am missing something?

A simple count should work:
select DISTINCT s.SUPPLIER_CODE AS SupplierCode 
count(*) from MEDMGR.CMTRS_TB_VALIDATED_SUPPLIERS s, MEDMGR.CMTRS_TO_BE_VALIDATED v, MEDMGR.CMTR_VALIDATION_SUPPLIER_LIST l where s.CMTR_ELID=v.CMTR_ELID and RECORD_DATE > 1/1/2014 and l.SUPPLIER_CODE= s.SUPPLIER_CODE and l.QUALITY_INDEX != -1
group by s.SUPPLIER_CODE

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40364438
why would you use DISTINCT with GROUP BY?

The GROUP BY makes the results distinct by itself
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40364443
You wouldn't.  Just forgot to remove it on the copy/paste.  ;)
0
 

Author Comment

by:Mike Miller
ID: 40364460
SELECT  s.supplier_code AS suppliercode, count(*) AS suppliercodecn
  FROM medmgr.cmtrs_tb_validated_suppliers s,
       medmgr.cmtrs_to_be_validated v,
       medmgr.cmtr_validation_supplier_list l
 WHERE s.cmtr_elid = v.cmtr_elid
   AND record_date > 1 / 1 / 2014
   AND l.supplier_code = s.supplier_code
   AND l.quality_index != -1
   group by s.supplier_code

returns a count of every time a result is in the table, not the ones that follow the WHERE clause
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40364471
>>> returns a count of every time a result is in the table, not the ones that follow the WHERE clause

no - my query does the grouping AFTER applying the where clause (so does slightwv's )

Is that what you want?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40364481
If you want the counting to be applied before the where clause, do you still want the counts for each supplier_code or do you want them for the entire result set?

or, maybe you want the distinct values, but want the count to be for the entire results if so try this...

SELECT  distinct s.supplier_code AS suppliercode, count(*) over() AS suppliercodecn
  FROM medmgr.cmtrs_tb_validated_suppliers s,
       medmgr.cmtrs_to_be_validated v,
       medmgr.cmtr_validation_supplier_list l
 WHERE s.cmtr_elid = v.cmtr_elid
   AND record_date > 1 / 1 / 2014
   AND l.supplier_code = s.supplier_code
   AND l.quality_index != -1


Note, the DISTINCT is intentional, as is the removal of the GROUP BY
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40364482
>>returns a count of every time a result is in the table, not the ones that follow the WHERE clause

Please post sample data and expected results.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40364487
select COUNT(DISTINCT s.SUPPLIER_CODE) AS SupplierCode, COUNT(s.SUPPLIER_CODE) AS SupplierCodeCn
from MEDMGR.CMTRS_TB_VALIDATED_SUPPLIERS s, MEDMGR.CMTRS_TO_BE_VALIDATED v, MEDMGR.CMTR_VALIDATION_SUPPLIER_LIST l
where s.CMTR_ELID=v.CMTR_ELID and RECORD_DATE > 1/1/2014 and l.SUPPLIER_CODE= s.SUPPLIER_CODE an
0
 

Author Comment

by:Mike Miller
ID: 40364499
VALUES

SUPPLIER_CODE          RECORD_DATE
SupplierA              1/2/2014
SupplierA              1/2/2014
SupplierA              12/31/2012
SupplierB              12/31/2012
SupplierB              1/2/2014
SupplierC              1/2/2014
SupplierC              1/2/2014
SupplierC              1/2/2014
SupplierC               12/31/2011

RESULTS

SUPPLIERCODE              SUPPLIERCODECN
SupplierA                            2
SupplierB                             1
SupplierC                             3
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40364508
The query I oringinally posted should work for that

If it doesn't please post sample data where it fails, please include representative data from all 3 tables so I can replicate the conditions
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40364531
or if code and date are all that matter then
 rather than supplying all of the data, at least supply these results, along with the expected counts.


SELECT  s.supplier_code ,  record_date
  FROM medmgr.cmtrs_tb_validated_suppliers s,
       medmgr.cmtrs_to_be_validated v,
       medmgr.cmtr_validation_supplier_list l
 WHERE s.cmtr_elid = v.cmtr_elid
   AND record_date > 1 / 1 / 2014
   AND l.supplier_code = s.supplier_code
   AND l.quality_index != -1
0
 

Author Comment

by:Mike Miller
ID: 40364562
My bad guys. I forgot the date is in UNIX TS format. I convert it within an app. Once I used 1412476555 as a date (sometime yesterday) I got more desired results with the 1st response. Thanks, sorry for the long thread. I've actually got another question about this query, i'll open another question.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40364573
I was just about to ask about the date since   "1 / 1 / 2014" looked odd , I was assuming that was just pseudo code for a value that would be a variable but if it was literally what was there then it makes sense it would produce odd results.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40365461
I have included TRUNC(record_date/86400) so that you are counting the distinct days only (i.e. time of day is removed as a factor) - you can remove it if the information has only midnight as the time.

Not sure how 1/1/2014 is working for you , but I have used  (date '2014-01-01' - date '1970-01-01' ) * 60 * 60 * 24
for the unix timestamp

Variant 1: without changing the join syntax:
SELECT
      supplier_code
    , max(day_count)  AS SUPPLIERCODECN
FROM (
        SELECT
              s.supplier_code
            , COUNT(*) OVER(PARTITION BY s.supplier_code, TRUNC(record_date/86400)) AS day_count
        FROM medmgr.cmtrs_tb_validated_suppliers s
           , medmgr.cmtrs_to_be_validated v
           , medmgr.cmtr_validation_supplier_list l
        WHERE s.cmtr_elid = v.cmtr_elid
              AND record_date > (date '2014-01-01' - date '1970-01-01' ) * 60 * 60 * 24
              AND l.supplier_code = s.supplier_code
              AND l.quality_index != -1
      )
GROUP BY
      s.supplier_code
;

Open in new window


and, Variant 2: by using "more recent"* join syntax:
SELECT
      supplier_code
    , max(day_count)  AS SUPPLIERCODECN
FROM (
        SELECT
              s.supplier_code
            , COUNT(*) OVER(PARTITION BY s.supplier_code, TRUNC(record_date/86400)) AS day_count
            FROM medmgr.cmtrs_tb_validated_suppliers s
            INNER JOIN medmgr.cmtrs_to_be_validated v         ON  s.cmtr_elid = v.cmtr_elid
            INNER JOIN medmgr.cmtr_validation_supplier_list l ON  s.supplier_code = l.supplier_code
                                                              AND l.quality_index != -1
            WHERE record_date > (date '2014-01-01' - date '1970-01-01' ) * 60 * 60 * 24
     )
GROUP BY
      s.supplier_code
;

Open in new window


*only 22 years old
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40367547
curious:  the accepted answer does not agree with the result provided here at http:#a40364499

SUPPLIER_CODE          RECORD_DATE
SupplierA              1/2/2014
SupplierA              1/2/2014
SupplierA              12/31/2012
SupplierB              12/31/2012
SupplierB              1/2/2014
SupplierC              1/2/2014
SupplierC              1/2/2014
SupplierC              1/2/2014
SupplierC               12/31/2011


RESULTS

SUPPLIERCODE              SUPPLIERCODECN
SupplierA                            2
SupplierB                             1
SupplierC                             3

A simple count of the records (such as in the accepted answer) would result in:

SUPPLIERCODE              SUPPLIERCODECN
SupplierA                            3 (not 2)
SupplierB                            2 (not 1)
SupplierC                            4 (not 3)


And there was no discussion or attempt to address the Unix Timestamp values either...
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question