How to combine select * statement with count statement?

Question

4.00/5 (1 vote)

See more:

I have two tables :-

Product and Order_Details
    
    
         Product                     Order_Details
        -----------------         -----------------
        Product_ID (int)           Order_ID (int)
        Product_Name(varchar)      Product_Name(varchar)
        Product_Spec

Sample Data :-

Order_Details
        -----------------
    OrderID	    Product_Name
    1	         Alpha
    2	         Alpha
    3	         Alpha
    4            Bravo
    5            Charlie

Product
    -----------------
    Product_ID	    Product_Name      Product_Spec 
        1	         Alpha              Good
        2	         Bravo              Excellent
        3	         Charlie            Mediocre

I have written two SQL queries :-

Query 1:

SQL

Select * from Product

Query 2:

SQL

select top 1 Product_Name, count(*) as cnt from [Order_Details] group by Product_Name order by count(*) desc INNER JOIN [Product] on (Order_Details.Product_Name = Product.Product_Name)

First Query Results return :-

Product_ID	    Product_Name      Product_Spec 
            1	         Alpha              Good
            2	         Bravo              Excellent
            3	         Charlie            Mediocre

Second Query Results return :-
        Product_Name	    cnt 
        ------------       -----
          Alpha              3

Desired output :-

Product_ID      Product_Name      Product_Spec
     1             Alpha             Good

What I have tried:

I'm trying to display all values available (select *)in [Product] table based on the count operation I have done on [Order_Details] and Query2. Any ideas on how to do this?

Posted 13-Oct-17 23:09pm

Mike K Ng

Updated 19-Oct-17 12:56pm

OriginalGriff

v2

Add a Solution

2 solutions

Solution 2

|I am not going to suggest schema changes, but instead try and show how this can be achieved. Sometimes we need to query existing tables where we do not have the liberty to make some of the changes suggested.

From my ready of the question you are attempting the following (pseudo code)

SQL

Select * From Product where Product = (most common sold item in Order_Details)

My suggested approach is below

SQL

SELECT * FROM Product
WHERE Product in (Select Product FROM 
    (SELECT TOP 1 Product, Count(Product) 
     FROM Order_Details 
     GROUP by Product 
     ORDER by Count(Product) desc))

This may not be the most elegant way of doing things, but sometimes when a one-off query is needed it is helpful to be able to get a quick answer as shown above.

Happy coding.

Posted 19-Oct-17 12:56pm

Andrew Ussher

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-10-14T00:02:00

The first thing to notice is that your database design is wrong: you should not be storing the product name in your Order_Details table, but the Product ID instead, as a Foreign Key to the Products table:
Products:

ID     INT
Name   NVARCHAR
Spec   NVARCHAR

Orders:

ID     INT
PID    INT, Foreign key

And you probably need extra info in your Orders table: some kind of link to the customer, and to other items in the order, date, that kind of thing.
Normally, you'd have three or four tables here: Products, Customers, Orders, OrderDetails and they would be related:
OrderDetails has foreign keys to Products and Orders.
Orders has a foreign key to Customers.

Then you'd collect your info as

SQL

SELECT PID, COUNT(PID) AS OrderCount FROM OrderDetails GROUP BY PID

Which would return

1    1
2    1
3    1

For your data.
You then use a JOIN onto that to get the info you want:

SQL

SELECT TOP 1 p.ID, p.Name, p.Spec FROM Products p
JOIN (SELECT PID, COUNT(PID) AS OrderCount FROM OrderDetails GROUP BY PID) od
   ON p.ID = od.PID
ORDER BY od.OrderCount DESC