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

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?
Mike MillerSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
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
sdstuberCommented:
why would you use DISTINCT with GROUP BY?

The GROUP BY makes the results distinct by itself
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

slightwv (䄆 Netminder) Commented:
You wouldn't.  Just forgot to remove it on the copy/paste.  ;)
0
Mike MillerSoftware EngineerAuthor Commented:
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
sdstuberCommented:
>>> 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
sdstuberCommented:
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
slightwv (䄆 Netminder) Commented:
>>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
Scott PletcherSenior DBACommented:
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
Mike MillerSoftware EngineerAuthor Commented:
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
sdstuberCommented:
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
sdstuberCommented:
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
Mike MillerSoftware EngineerAuthor Commented:
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
sdstuberCommented:
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
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.