PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] SQL - Struggling and struggling with this task-VBForums
Results 1 to 7 of 7

Thread: [RESOLVED] SQL - Struggling and struggling with this task

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2018
    Posts
    205

    Resolved [RESOLVED] SQL - Struggling and struggling with this task

    Hi
    I am unable to produce this result:

    Name:  2019-03-16_19-52-48.jpg
Views: 99
Size:  24.0 KB

    Any help is appreciated.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,658

    Re: SQL - Struggling and struggling with this task

    You sure, your desired result is correct?
    I count 4 "A"'s and 3 "D"'s

    SQL Code:
    1. SELECT
    2. classification,
    3. COUNT(classification) AS total,
    4. SUM(CASE WHEN replanned='yes' THEN 1 ELSE 0 END) AS 'count replanned'
    5. FROM
    6. (SELECT * FROM 'open'
    7. UNION ALL
    8. SELECT * FROM 'closed'
    9. ORDER BY
    10. classification)
    11. GROUP BY
    12. classification
    Last edited by Zvoni; Mar 16th, 2019 at 04:10 PM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,658

    Re: SQL - Struggling and struggling with this task

    WHat the blazes is it again with the forum????

    Can't reply, had to edit.
    Now i can't edit, but reply again?

    The above returns
    classification total count replanned
    A 4 1
    B 2 1
    C 1 1
    D 3 0
    F 1 1
    H 1 0
    K 2 1
    L 1 0
    N 1 0
    U 1 0
    Z 1 1



    EDIT: the desired result is different to mine.
    There are 2 "K"'s, one of which is replanned
    Last edited by Zvoni; Mar 16th, 2019 at 04:29 PM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Nov 2018
    Posts
    205

    Re: SQL - Struggling and struggling with this task

    Thank you for your help. Right about A counts, I have seen that error before
    I tried to run it on the actual tables and I got this error:

    Code:
    Msg 1033, Level 15, State 1, Line 9
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
    then I tired to remove that "order by" to see if taht helps and I got this:
    Code:
    Msg 156, Level 15, State 1, Line 10
    Incorrect syntax near the keyword 'GROUP'.
    My tables are in a sql database
    Code:
    Microsoft SQL Server Standard Edition (64-bit)

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,658

    Re: SQL - Struggling and struggling with this task

    Try this amended version:
    Code:
    SELECT classification,
           COUNT(classification) AS total, 
           SUM(CASE WHEN replanned='yes' THEN 1 ELSE 0 END) AS 'count replanned'
    FROM 
        (SELECT * FROM [open]
        UNION ALL 
        SELECT * FROM [closed] ) 
    GROUP BY classification
    ORDER BY classification

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,658

    Re: SQL - Struggling and struggling with this task

    Quote Originally Posted by Grand View Post
    Thank you for your help. Right about A counts, I have seen that error before
    I tried to run it on the actual tables and I got this error:

    Code:
    Msg 1033, Level 15, State 1, Line 9
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
    then I tired to remove that "order by" to see if taht helps and I got this:
    Code:
    Msg 156, Level 15, State 1, Line 10
    Incorrect syntax near the keyword 'GROUP'.
    My tables are in a sql database
    Code:
    Microsoft SQL Server Standard Edition (64-bit)
    Ah, well. I did the fiddling with an SQLite-Database, so there you have it with the tablenames in [table] for MSSQL versus 'table' in SQLite
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Nov 2018
    Posts
    205

    Re: SQL - Struggling and struggling with this task

    Thanks, I tried "si_the_geek"'s solution and it says this now:

    Code:
    Msg 156, Level 15, State 1, Line 8
    Incorrect syntax near the keyword 'GROUP'.
    Thanks, I tried "si_the_geek"'s solution and it says this now:

    Code:
    Msg 156, Level 15, State 1, Line 8
    Incorrect syntax near the keyword 'GROUP'.
    Ok, I got it. W ejust needed to introduce that subquery as a table:

    Code:
    SELECT classification,
           COUNT(classification) AS total, 
           SUM(CASE WHEN replanned='yes' THEN 1 ELSE 0 END) AS 'count replanned'
    FROM 
        (SELECT * FROM [open]
        UNION ALL 
        SELECT * FROM [closed] ) As resultTable
    GROUP BY classification
    ORDER BY classification
    It runs without error. I just need to verify the result. Thanks so far
    Last edited by Grand; Mar 18th, 2019 at 04:21 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width