Solved

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

Posted on 2014-10-06
16
222 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:mwmiller78
  • 7
  • 3
  • 3
  • +2
16 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
why would you use DISTINCT with GROUP BY?

The GROUP BY makes the results distinct by itself
0
 
LVL 76

Expert Comment

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

Author Comment

by:mwmiller78
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
>>> 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 73

Expert Comment

by:sdstuber
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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:mwmiller78
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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:mwmiller78
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server 2016 Developers Edition Install 4 55
Database Containment - Benefits 6 24
BULK LOGGED - log full 9 10
Test a query 23 10
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now