0

I have 3 tables a, b, and c. Table a contains the ids of the stores, their earning date, and count of sale (flight tickets).

Table b contains the id, sale date, and count of clothing orders.

Table c contains the id, date and total count.

SQL> select * from a;

STOREID          EARNINGDATE COUNT_FLIGHT_TICKETS
-------------------- ----------- ----------------
store01         14980000           10
store01         14980001           32
store02         14980000          134

SQL> select * from b;

STOREID          EARNINGDATE CLOTHES_SALE_COUNT
-------------------- ----------- ---------------
store01         14980000           6
store02         14980000           6

SQL> select * from c;

STOREID          EARNINGDATE TOTAL_SALE_COUNT
-------------------- ----------- -------------
store01         14980001        32
store01         14980000        16
store02         14980000       134

Given above the tables, I have to print all the stores ids, with their date of earning for total sale, flight sale, and clothing sale.

|StoreId | EarningDate | FlightCount | ClothingCount | TotalCount |

I have used below query, but failing to get the above.

select b.storeId , sum(a.COUNT_FLIGHT_TICKETS), 
  sum(b.CLOTHES_SALE_COUNT), sum (c.TOTAL_SALE_COUNT) 
from a 
full outer join b on a.storeId = b.storeId 
  and a.EarningDate = b.earningdate 
full outer join c on a.storeId = c.storeId 
  and a.earningDate = b.earningDate group by a.storeId;

This query does not give all the rows and having some bug.

STOREID          flight clothing        total
------       --------- --------- --------------------
store02           134        6           134
store01           52             12           48

Can someone help me to correct this query to get the expected output?

Improve this question
6
  • What is your expected outcome given your data sample? Commented Jul 4, 2017 at 2:23
  • Expected: Each row shall contain all the fights Sale, Clothing sale, total sale for a storeId based on the earningDate. If on a particular day numOfFlight booking are null, then mark it 0 (NVL will do the job). storeId, Date, numOfFlights, numOfCloths, TotalSale Commented Jul 4, 2017 at 2:27
  • How you group by a.storeId ? Commented Jul 4, 2017 at 2:29
  • it was a typing error. apologies. Commented Jul 4, 2017 at 2:32
  • Is the result you are showing the result you get or the result you want? Don't you get an error? I am pretty sure MySQL doesn't suppert full outer joins. Commented Jul 4, 2017 at 4:27

2 Answers 2

Reset to default
4

One option would be to take the UNION of the thee tables and then aggregate by store:

SELECT
    t.STOREID,
    t.EARNINGDATE,
    SUM(t.COUNT_FLIGHT_TICKETS) AS FlightCount,
    SUM(t.CLOTHES_SALE_COUNT)   AS ClothingCount,
    SUM(t.TOTAL_SALE_COUNT)     AS TotalCount
FROM
(
    SELECT
        STOREID,
        EARNINGDATE,
        COUNT_FLIGHT_TICKETS,
        0 AS CLOTHES_SALE_COUNT,
        0 AS TOTAL_SALE_COUNT
    FROM a
    UNION ALL
    SELECT STOREID, EARNINGDATE, 0, CLOTHES_SALE_COUNT, 0
    FROM b
    UNION ALL
    SELECT STOREID, EARNINGDATE, 0, 0, TOTAL_SALE_COUNT
    FROM c
) t
GROUP BY
    t.STOREID,
    t.EARNINGDATE

This gets around the join problem you correctly pointed out, which might require a full outer join. Full outer join in MySQL is a hassle, and in any case it usually should not be necessary with good design.

Demo here:

Rextester

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Seems something wrong, I am still getting wrong data.STOREID FlightCount ClothingCount TotalCount -------------------- --------------- ---------- ---------- store02 274 0 0 store01 96 0 0
@Jay Maybe your data has a problem.
It works, thanks.Can we modify to include it to take the date? Store sale for each date?
@Jay Taking the date doesn't make sense unless you want an aggregate of the date, e.g. the min or max.
Yes, I want aggregated sum of each type of earning, per storeId, per date.
|
3

Assuming that for each table, (storeId, earningdate) is unique or compound keys, group by will be unnecessary. You can try this query.

select 
    IF(isnull(a.STOREID), IF(isnull(b.STOREID), c.STOREID, b.STOREID),a.STOREID) as StoreId,
    IF(isnull(a.EARNINGDATE), IF(isnull(b.EARNINGDATE), c.EARNINGDATE, b.EARNINGDATE),a.EARNINGDATE) as EarningDate, 
    IF(isnull(COUNT_FLIGHT_TICKETS),0,COUNT_FLIGHT_TICKETS) as FlightCount, 
    IF(isnull(CLOTHES_SALE_COUNT),0,CLOTHES_SALE_COUNT) as ClothingCount, 
    IF(isnull(TOTAL_SALE_COUNT),0,TOTAL_SALE_COUNT) as TotalCount  
from a full outer join b 
    on a.storeId = b.storeId and a.EarningDate = b.earningdate
    full outer join c 
       on a.storeId = c.storeId and a.earningDate = c.earningDate;

Result was:

STOREID   EarningDate    flight    clothing        total
------   -------------  --------- --------- --------------------
store01      14980000      10         6             16
store02      14980000      134        6             134
store01      14980001      32         0             32

Is this your expected result? I think you forgot the earning date.

Given above the tables, I have to print all the stores ids, with their date of earning for total sale, flight sale, and clothing sale.

|StoreId | EarningDate | FlightCount | ClothingCount | TotalCount |

Improve this answer

6 Comments

Yes, This is what I am expecting. Thanks a lot. I will try and let you know.
Why we are using left join here? Actually storeId, and dateOf earning is not unique here. A storeId may have multiple earning on the same date for same type of sale. So we need to take the aggregated sum of that particular type of sale for each date.
I can see duplicate entries for the problem explained here.
This won't work if the b and c tables could have stores or dates which do not appear in the a table. The OP was right with regard to the need for a full outer join if you want to use a join approach.
@TimBiegeleisen I think your right. My problem was that the outer join isn't working in my mysql. If it is working for him, he can use the full outer join.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.