CODEWITHSUNDEEP
mysqlsql
LukeDS
LukeDS

Reputation: 141

Return multiple selected value inside one query?

Sorry for title excuse my ignorance in developing a straight to the point title.

This code/scenario is simply for understanding where I'm coming from (I will not post real code), i'm not asking to evaluate this code nor to debug, there might be index errors etc. this was written in here without testing.

The tables are fabricated in here too, so if they look silly and doesn't make sense, you are right. But please try to keep in mind of what the problem is as that is universal and can be applied to any database schema in real world.

Problem

I need to count how many times a staff has sold to a particular customer, as well as return last item sold from that customer. The bold words are the problematic bit. I don't know how to create this bit of the query without damaging the counting part (How many customer a seller sold to), I tried using Order By but didnt returned what I needed.

 SELECT StaffName, Count(SoldToCustomerId)
 AS TimesSoldToCustomer, CustomerName, Item FROM CustomerHistory
 INNER JOIN Seller ON SoldToCustomerId = CustomerId
 GROUP BY SoldToCustomerId;

Database

CustomerHistory
CustomerId     CustomerName   PurchasedDate  Item
1              John           01/02/2018     Iphone 
2              Tom            02/02/2018     Galaxy
3              Peter          03/02/2018     Ps4
1              John           05/02/2018     Xbox One
1              John           06/02/2018     Ps4
1              John           03/02/2018     PC
1              John           07/01/2017     graphic card

Seller
StaffId   StaffName     SoldToCustomerId
1         James         1
2         Tim           2
..


Ideal result from sql query
StaffName   TimesSoldToCustomer    CustomerName   lastSoldItem
James       5                      John           Ps4    -- Last Item Sold
Tim         1                      Tom            Galaxy -- Last Item Sold

Upvotes: 0

Views: 57

Answers (4)

Wender Alves
Wender Alves

Reputation: 51

For the result you want, there are many ways to do it, however, all using subquerys. See the one suggestion

SELECT 
        StaffName,
        b.qtd AS TimesSoldToCustomer,
        a.CustomerName,
        b.Item AS lastSoldItem
    FROM CustomerHistory AS a
    INNER JOIN Seller ON SoldToCustomerId = CustomerId
    LEFT JOIN (SELECT
                    Count(DISTINCT a.CustomerId) AS qtd,
                    a.CustomerName,
                    (SELECT
                        c.Item
                    FROM CustomerHistory AS c
                    WHERE c.CustomerName = a.CustomerName AND c.PurchasedDate = MAX(a.PurchasedDate)) AS Item
                    FROM CustomerHistory AS a
                    GROUP BY
                    a.CustomerName) AS b ON b.CustomerName = a.CustomerName
    GROUP BY SoldToCustomerId;

Upvotes: 1

MatBailie
MatBailie

Reputation: 86716

As an aside, please always qualify your column references to make clear which table they come from.

My approach is to do it in two steps.

  1. Find out how many times the seller sold to the customer, and the date of the last sale
  2. Join on the history data again to find out what was sold on that latest date

It does assume that no customer purchases more than one item on any given date.

And, as requested, I've tried to ignore that the data structure is insanely bad ;)

(For example, if someone purchases from more than one seller, this data model breaks. Because you can't tell which sale record corresponds to which seller.)

SELECT
    s.*,
    h.customerName,
    h.item
FROM
(
    SELECT
        s.StaffName,
        s.CustomerID,
        COUNT(*)               AS TimesSoldToCustomer,
        MAX(h.PurchasedDate)   AS LastPurchasedDate
    FROM
        Seller            AS s
    INNER JOIN
        CustomerHistory   AS h
            ON s.SoldToCustomerId = h.CustomerId
    GROUP BY
        s.StaffName,
        s.CustomerID
)
    AS s
INNER JOIN
    CustomerHistory   AS h
        ON  s.SoldToCustomerId  = h.CustomerId
        AND s.LastPurchasedDate = h.PurchaseDate

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use join and subquery like below

 select a.CustomerName,a.TimesSoldToCustomer ,
 s.StaffName     ,c.Item
 from  (
  SELECT  CustomerName, Count(SoldToCustomerId) as TimesSoldToCustomer 
  ,min(CustomerId) as  CustomerId  
  FROM CustomerHistory group by CustomerName
 ) a join Seller s on a.CustomerId =s.SoldToCustomerId
   join ( select CustomerName,PurchasedDate,Item
             from CustomerHistory t1 where PurchasedDate=( select max(PurchasedDate)
                          from CustomerHistory t2 where 
                           t1.CustomerName=t2.CustomerName)
        ) c on a.CustomerName=c.CustomerName

Upvotes: 1

Andrew Ebert
Andrew Ebert

Reputation: 62

I would suggest using a sub-query to return the last item sold. Should be easier to write with actual data but I would basically order by the PurchaseDate in desc order.

Upvotes: 0

Related Questions