Okay
  Public Ticket #2207791
SQL Tables, Several TABLES to be joined
Closed

Comments

  • Mike Ziuzin started the conversation
    November 7, 2019 at 12:25pm

    I have several SELECT queries as a result several one line data tables (works very well), see attached picture.

    Please help us to make single Data Table for these three lines.

    EXAMPLE of Single Table Query.

    SELECT 'Shots'
    ,SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), 1) then 1 else 0 end) as 'Day'
    ,SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), WEEKDAY(CURDATE())) then 1 else 0 end) as 'Week'
    ,COUNT(i4HockeyShootingResultShots.ResultScore) as 'Month'
    FROM i4HockeyShootingResultShots
    JOIN i4HockeyShootingSessions
    ON i4HockeyShootingResultShots.SessionID = i4HockeyShootingSessions.SessionID
    WHERE i4HockeyShootingSessions.PlayerID = %CURRENT_USER_ID%
    AND i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), DAYOFMONTH(CURDATE()))
    GROUP BY i4HockeyShootingSessions.PlayerID


    UNION works but just for simple cases. 

    For my complex query it does not work. Every single SELECT query in the below UNION-query works well, but all together with UNION not.

    SELECT 'Shots'

    ,SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), 1) then 1 else 0 end) as 'Day'

    ,SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), WEEKDAY(CURDATE())) then 1 else 0 end) as 'Week'

    ,COUNT(i4HockeyShootingResultShots.ResultScore) as 'Month'

    FROM i4HockeyShootingResultShots

    JOIN i4HockeyShootingSessions

    ON i4HockeyShootingResultShots.SessionID = i4HockeyShootingSessions.SessionID

    WHERE i4HockeyShootingSessions.PlayerID = %CURRENT_USER_ID%

    AND i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), DAYOFMONTH(CURDATE()))

    GROUP BY i4HockeyShootingSessions.PlayerID

    UNION

    SELECT 'Goals'

    ,SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), 1) then 1 else 0 end) as 'Day'

    ,SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), WEEKDAY(CURDATE())) then 1 else 0 end) as 'Week'

    ,COUNT(i4HockeyShootingResultShots.ResultScore) as 'Month'

    FROM i4HockeyShootingResultShots

    JOIN i4HockeyShootingSessions

    ON i4HockeyShootingResultShots.SessionID = i4HockeyShootingSessions.SessionID

    WHERE i4HockeyShootingSessions.PlayerID = %CURRENT_USER_ID%

    AND i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), DAYOFMONTH(CURDATE()))

    AND i4HockeyShootingResultShots.ResultPercent>0

    GROUP BY i4HockeyShootingSessions.PlayerID

    UNION

    SELECT 'Accuracy'

    ,SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), 1) then i4HockeyShootingResultShots.ResultPercent else 0 end)

    / SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), 1) then i4HockeyShootingSessions.PucksCount else 0 end) as 'Day'

    ,SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), WEEKDAY(CURDATE())) then i4HockeyShootingResultShots.ResultPercent else 0 end)

    / SUM(case when i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), WEEKDAY(CURDATE())) then i4HockeyShootingSessions.PucksCount else 0 end)as 'Week'

    ,SUM(i4HockeyShootingResultShots.ResultPercent) / SUM(i4HockeyShootingSessions.PucksCount)  as 'Month'

    FROM i4HockeyShootingResultShots

    JOIN i4HockeyShootingSessions

    ON i4HockeyShootingResultShots.SessionID = i4HockeyShootingSessions.SessionID

    WHERE i4HockeyShootingSessions.PlayerID = %CURRENT_USER_ID%

    AND i4HockeyShootingSessions.StartTime > SUBDATE(CURDATE(), DAYOFMONTH(CURDATE()))

    GROUP BY i4HockeyShootingSessions.PlayerID


  •  2,572
    Aleksandar replied
    November 8, 2019 at 7:32am

    Hey Mike.

    Thank you for your purchase.

    At the end of a query with several UNION statements, simply add UNION ALL, and it should work just fine.

    Please try that, and let me know.

    Kind Regards, 

    Aleksandar Vuković
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  • Mike Ziuzin replied
    November 8, 2019 at 9:36am

    Thank you very much! It works. My fault.

  •  2,572
    Aleksandar replied
    November 8, 2019 at 2:34pm

    No worries Mike.

    I'm glad to have been of service.

    If you have any further questions or issues, please feel free to open a new ticket, and we'll gladly help.

    Kind Regards, 

    Aleksandar Vuković
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables