none
Creating SQL summary report

    질문

  • 


    I have table above name tblstudent.

    The result i want to get is shown below:

    ignore the values of the table, it's just an example.

    what i've tried so far:

    in MS Access i used this code though not totally the output i want.

    TRANSFORM Count(tblstudent.[ID]) AS CountOfID
    SELECT tblstudent.[en_gender], Count(tblstudent.[ID]) AS [Total Of ID]
    FROM tblstudent
    GROUP BY tblstudent.[en_gender]
    PIVOT tblstudent.[en_category];

    Im having a problem with translating the msaccess sql code to sql or mysql query. and is there any other query you could suggest to get the output i've shown above?

    2018년 5월 18일 금요일 오전 2:57

답변

  •  msaccess sql code to sql or mysql query.

    since you're asking for mysql code may be this is what you can try

    SELECT en_gender,
    SUM(CASE WHEN en_category = 'Technical' THEN 1 ELSE 0 END) AS Technical,
    SUM(CASE WHEN en_category = 'Non-Technical' THEN 1 ELSE 0 END) AS NonTechnical,
    SUM(CASE WHEN en_category = 'HS/Vocational' THEN 1 ELSE 0 END) AS [HS/Vocational],
    SUM(1) AS Total
    FROM tblstudent
    GROUP BY en_gender
    UNION ALL
    SELECT 'INDUSTRIAL',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'INDUSTRIAL'
    UNION ALL
    SELECT 'ENGINEERING',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'ENGINEERING'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    thanks visakh16,

    here's the result my your query:

    When i sum the technical,nontechnical, and hs/vocational i got 200 which is not tally with the total output result.

    I have industrial and engineering student but it was null in the result.

    For total to work correctly you need see if you've any other categories coming other than Technical,Non_technical and HS/Vocational.

    If yes, you need to make total like below

    SUM(CASE WHEN en_category IN ('Technical','NonTechnical','HS/Vocational') THEN 1 ELSE 0 END) AS Total

    Also for the INDUSTRIAL getting NULL you need to ensure the value in your en_field is exactly INDUSTRIAL without any spaces or any other characters being present

    You can also try like this

    SELECT en_gender,
    SUM(CASE WHEN en_category = 'Technical' THEN 1 ELSE 0 END) AS Technical,
    SUM(CASE WHEN en_category = 'Non-Technical' THEN 1 ELSE 0 END) AS NonTechnical,
    SUM(CASE WHEN en_category = 'HS/Vocational' THEN 1 ELSE 0 END) AS [HS/Vocational],
    SUM(CASE WHEN en_category IN ('Technical','Non-Technical','HS/Vocational') THEN 1 ELSE 0 END) AS Total
    FROM tblstudent
    GROUP BY en_gender
    UNION ALL
    SELECT 'INDUSTRIAL',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field LIKE 'INDUSTRIAL%'
    UNION ALL
    SELECT 'ENGINEERING',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'ENGINEERING'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 zirj0hn 2018년 5월 21일 월요일 오전 8:46
    2018년 5월 21일 월요일 오전 5:58

모든 응답

  • in MS Access i used this code though not totally the output i want.

    Hello,

    You can't get this required result in any RDBM with plain SQL, to format it this way is the task of the frontend, like a report in MS Access.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    2018년 5월 18일 금요일 오전 4:34
  • Close one in TSQL:

    create table tblstudent (id int, en_Category varchar(20),en_gender varchar(6),en_field  varchar(20))
    
    insert into tblstudent (id, en_Category,en_gender,en_field )
    values(1,'Non-Technical','Female','INDUSTRIAL')
    ,(2,'Non-Technical','Female','INDUSTRIAL')
    ,(3,'HS/Vocational','Male','INDUSTRIAL')
    ,(4,'Technical','Male','INDUSTRIAL')
    ,(5,'HS/Vocational','Female','INDUSTRIAL')
    ,(6,'Technical','Male','ENGINEERING')
    ,(7,'HS/Vocational','Male','INDUSTRIAL')
    ,(8,'Technical','Female','ENGINEERING')
    
     
    
     Select ISNULL(en_gender,'Total') en_gender
    ,Sum(Case when en_Category='Technical' then 1 else null End) [Technical]
    ,Sum(Case when en_Category='Non-Technical' then 1 else null End) [Non-Technical]
    ,Sum(Case when en_Category='HS/Vocational' then 1 else null End) [HS/Vocational]
    ,Sum(1) 'Total' from tblstudent
    Group by GROUPING SETS(	(en_gender),())
     Union ALL 
    Select en_field , null as [Technical], null as [Non-Technical], null as [HS/Vocational],  COUNT(*) [Total] from tblstudent
    Group by en_field
     
    
    drop table tblstudent

    2018년 5월 18일 금요일 오후 5:53
    중재자
  •  msaccess sql code to sql or mysql query.

    since you're asking for mysql code may be this is what you can try

    SELECT en_gender,
    SUM(CASE WHEN en_category = 'Technical' THEN 1 ELSE 0 END) AS Technical,
    SUM(CASE WHEN en_category = 'Non-Technical' THEN 1 ELSE 0 END) AS NonTechnical,
    SUM(CASE WHEN en_category = 'HS/Vocational' THEN 1 ELSE 0 END) AS [HS/Vocational],
    SUM(1) AS Total
    FROM tblstudent
    GROUP BY en_gender
    UNION ALL
    SELECT 'INDUSTRIAL',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'INDUSTRIAL'
    UNION ALL
    SELECT 'ENGINEERING',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'ENGINEERING'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 7:35
  •  msaccess sql code to sql or mysql query.

    since you're asking for mysql code may be this is what you can try

    SELECT en_gender,
    SUM(CASE WHEN en_category = 'Technical' THEN 1 ELSE 0 END) AS Technical,
    SUM(CASE WHEN en_category = 'Non-Technical' THEN 1 ELSE 0 END) AS NonTechnical,
    SUM(CASE WHEN en_category = 'HS/Vocational' THEN 1 ELSE 0 END) AS [HS/Vocational],
    SUM(1) AS Total
    FROM tblstudent
    GROUP BY en_gender
    UNION ALL
    SELECT 'INDUSTRIAL',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'INDUSTRIAL'
    UNION ALL
    SELECT 'ENGINEERING',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'ENGINEERING'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    thanks visakh16,

    here's the result my your query:

    When i sum the technical,nontechnical, and hs/vocational i got 200 which is not tally with the total output result.

    I have industrial and engineering student but it was null in the result.

    2018년 5월 21일 월요일 오전 2:18
  •  thanks visakh16,

    here's the result my your query:

    When i sum the technical,nontechnical, and hs/vocational i got 200 which is not tally with the total output result.

    I have industrial and engineering student but it was null in the result.

    Hi zirj0hn,

    Please try this.

    ;WITH CTE AS
    (
    SELECT en_gender,
    SUM(CASE WHEN en_category = 'Technical' THEN 1 ELSE 0 END) AS Technical,
    SUM(CASE WHEN en_category = 'Non-Technical' THEN 1 ELSE 0 END) AS NonTechnical,
    SUM(CASE WHEN en_category = 'HS/Vocational' THEN 1 ELSE 0 END) AS [HS/Vocational],
    SUM(CASE WHEN en_category='Technical' OR en_category='Non-Technical' OR en_category='HS/Vocational' THEN 1 ELSE 0 END) AS Total
    FROM tblstudent
    GROUP BY en_gender
    )
    SELECT en_gender,
    SUM(CASE WHEN en_category = 'Technical' THEN 1 ELSE 0 END) AS Technical,
    SUM(CASE WHEN en_category = 'Non-Technical' THEN 1 ELSE 0 END) AS NonTechnical,
    SUM(CASE WHEN en_category = 'HS/Vocational' THEN 1 ELSE 0 END) AS [HS/Vocational],
    SUM(CASE WHEN en_category='Technical' OR en_category='Non-Technical' OR en_category='HS/Vocational' THEN 1 ELSE 0 END) AS Total
    FROM tblstudent
    GROUP BY en_gender
    UNION ALL
    SELECT 'Total',SUM(Technical),SUM(NonTechnical),SUM([HS/Vocational]),SUM(Total)  FROM CTE
    UNION ALL
    SELECT 'INDUSTRIAL',
    NULL,
    NULL,
    NULL,
    COUNT(*)
    FROM tblstudent
    WHERE en_field = 'INDUSTRIAL'
    UNION ALL
    SELECT 'ENGINEERING',
    NULL,
    NULL,
    NULL,
    COUNT(*)
    FROM tblstudent
    WHERE en_field = 'ENGINEERING'

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 5월 21일 월요일 오전 2:57
  •  msaccess sql code to sql or mysql query.

    since you're asking for mysql code may be this is what you can try

    SELECT en_gender,
    SUM(CASE WHEN en_category = 'Technical' THEN 1 ELSE 0 END) AS Technical,
    SUM(CASE WHEN en_category = 'Non-Technical' THEN 1 ELSE 0 END) AS NonTechnical,
    SUM(CASE WHEN en_category = 'HS/Vocational' THEN 1 ELSE 0 END) AS [HS/Vocational],
    SUM(1) AS Total
    FROM tblstudent
    GROUP BY en_gender
    UNION ALL
    SELECT 'INDUSTRIAL',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'INDUSTRIAL'
    UNION ALL
    SELECT 'ENGINEERING',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'ENGINEERING'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    thanks visakh16,

    here's the result my your query:

    When i sum the technical,nontechnical, and hs/vocational i got 200 which is not tally with the total output result.

    I have industrial and engineering student but it was null in the result.

    For total to work correctly you need see if you've any other categories coming other than Technical,Non_technical and HS/Vocational.

    If yes, you need to make total like below

    SUM(CASE WHEN en_category IN ('Technical','NonTechnical','HS/Vocational') THEN 1 ELSE 0 END) AS Total

    Also for the INDUSTRIAL getting NULL you need to ensure the value in your en_field is exactly INDUSTRIAL without any spaces or any other characters being present

    You can also try like this

    SELECT en_gender,
    SUM(CASE WHEN en_category = 'Technical' THEN 1 ELSE 0 END) AS Technical,
    SUM(CASE WHEN en_category = 'Non-Technical' THEN 1 ELSE 0 END) AS NonTechnical,
    SUM(CASE WHEN en_category = 'HS/Vocational' THEN 1 ELSE 0 END) AS [HS/Vocational],
    SUM(CASE WHEN en_category IN ('Technical','Non-Technical','HS/Vocational') THEN 1 ELSE 0 END) AS Total
    FROM tblstudent
    GROUP BY en_gender
    UNION ALL
    SELECT 'INDUSTRIAL',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field LIKE 'INDUSTRIAL%'
    UNION ALL
    SELECT 'ENGINEERING',
    NULL,
    NULL,
    NULL,
    SUM(1)
    FROM tblstudent
    WHERE en_field = 'ENGINEERING'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 zirj0hn 2018년 5월 21일 월요일 오전 8:46
    2018년 5월 21일 월요일 오전 5:58
  • Thanks Visakh16, hope to learn more from you :)
    2018년 5월 21일 월요일 오전 8:47