By Patiphon Ongartittichai


2020-08-01 13:16:26 8 Comments

I want to Retrieve customer names, total orders (how many time they order the products) and the total amount they're spent in the lifetime. Run a single query WITHOUT Join, group by, having operators. Show only customers who have at least one order.

Here is my database

Customer-    CustomerID| CustomerName   SalesOrder- SalesOrderID | CustomerID | SaleTotal
             100000    |  John                        1001       |   100000   |  2000
             200000    |  Jane                        1002       |   100000   |  3000  
             300000    |  Sean                        1003       |   200000   |  5000

When I query

SELECT CustomerName,count(*) AS Total_Orders,sum(SaleTotal) AS SaleTotal                           
FROM Customer C,SalesOrderHeader SH WHERE C.CustomerID=SH.CustomerID;

It show only one row. The answer that I want is

CustomerName | Total_Orders | SaleTotal
John            2               5000
Jane            1               5000

I just new on mysql. So does anyone here know how to do this?

3 comments

@Jim Macaulay 2020-08-01 13:28:48

You have to use below query. You cannot achieve it without join and group by

SELECT CustomerName,count(*) AS Total_Orders,sum(SaleTotal) AS SaleTotal                           
FROM Customer C,SalesOrderHeader SH WHERE C.CustomerID=SH.CustomerID
group by;

@GMB 2020-08-01 13:28:18

If you are to do this without joins and group by, then the simplest approach is to use correlated subqueries:

select *
from (
    select
        c.customerName,
        (
            select count(*) 
            from salesOrder so 
            where so.customerID = c.customerID
        ) totalOrders,
        (
            select sum(salesTotal) 
            from salesOrder so 
            where so.customerID = c.customerID
        ) saleTotal
    from customer c
) t
where totalOrders > 0

Note that this query is clearly suboptimal - because it scans the salesOrder table twice, while a single scan would suffice. A better way to write this would be:

select c.customerName, count(*) totalOrders, sum(salesTotal) saleTotal
from customer c
inner join saleOrder so on so.customerID = c.customerID
group by c.customerID, c.customerName

There is no need for a having clause here - the inner join filters out customers that have no order already.

@Gordon Linoff 2020-08-01 13:27:35

Use aggregation . . . and proper join syntax:

SELECT CustomerName, COUNT(*) AS Total_Orders, SUM(SaleTotal) AS SaleTotal                           
FROM Customer C JOIN
     SalesOrderHeader SH 
     ON C.CustomerID = SH.CustomerID
GROUP BY CustomerName;

Your query would fail in almost any database -- including newer versions of MySQL. You have mixed aggregated columns and unaggregated columns in the SELECT. The unaggregated ones should be in a GROUP BY.

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

@Karol Pawlak 2020-08-01 13:30:35

OP specified, that he doesnt want join and group by to be used in the query

Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] UTF-8 all the way through

2 Answered Questions

[SOLVED] Using group by on multiple columns

17 Answered Questions

[SOLVED] Select first row in each GROUP BY group?

14 Answered Questions

[SOLVED] MySQL Query GROUP BY day / month / year

29 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

22 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2166442 View
  • 1114 Score
  • 22 Answer
  • Tags:   sql oracle

27 Answered Questions

[SOLVED] Retrieving the last record in each group - MySQL

13 Answered Questions

[SOLVED] MySql export schema without data

  • 2011-05-30 11:06:54
  • Darth Blue Ray
  • 370367 View
  • 506 Score
  • 13 Answer
  • Tags:   mysql sql

13 Answered Questions

[SOLVED] Count(*) vs Count(1) - SQL Server

26 Answered Questions

[SOLVED] What is the difference between UNION and UNION ALL?

  • 2008-09-08 15:19:33
  • Brian G
  • 1345127 View
  • 1437 Score
  • 26 Answer
  • Tags:   sql union union-all

Sponsored Content