Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-10-06
16
Medium Priority
?
241 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
  • 7
  • 3
  • 3
  • +2
16 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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 78

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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 78

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 78

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 70

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

824 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