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

x
?
Solved

Getting a COUNT of records in a result set without using a GROUP BY clause (SQL query help)

Posted on 2014-10-06
3
Medium Priority
?
320 Views
Last Modified: 2014-10-06
I have the following query:

SELECT DISTINCT s.SUPPLIER_CODE AS SupplierCode, l.SUPPLIER_NAME AS SupplierName 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 > 1412476555  and l.SUPPLIER_CODE= s.SUPPLIER_CODE and l.QUALITY_INDEX != -1

Open in new window


(The date above is just a UNIX timestamp from yesterday)
It appears that I will not be able to use a GROUP BY clause.
I need to add a COUNT of the distinct SUPPLIER_CODE and the number of times it appears in the result set without using a GROUP BY in the statement.



Results should look like this...
SUPPLIERCODE           SUPPLIER_NAME       SUPPLIERCODECN
SupplierA                       WidgetCo101                        2
SupplierB                       Mikes Toy Co.                       1
SupplierC                       Goober Enterprises             3
0
Comment
Question by:Mike Miller
3 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 40364666
Why can't you use a group by?

To get a count based on a value, group by is the standard.

You might be able to muck up perfectly good SQL and make the select a lot more expensive to run but why would you?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40364725
similar to your previous question, simply extend the group by to include the name


 
SELECT s.supplier_code AS suppliercode, l.supplier_name AS suppliername, 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 > 1412476555
     AND l.supplier_code = s.supplier_code
     AND l.quality_index != -1
GROUP BY s.supplier_code, l.supplier_name

Open in new window


or, if the name might change for a given supplier code but you only want to show one, then you'll have to pick one.

Use could use an aggregate for that as well, for example MIN


 
 SELECT s.supplier_code AS suppliercode,
         MIN(l.supplier_name) AS suppliername,
         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 > 1412476555
     AND l.supplier_code = s.supplier_code
     AND l.quality_index != -1
GROUP BY s.supplier_code

Open in new window

0
 

Author Closing Comment

by:Mike Miller
ID: 40364746
You're right. I was thinking I couldn't use that clause, but it turns out I can. Thanks for bearing with me.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

879 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