locked
How to get Data which is not in table? RRS feed

  • Question

  • User992700523 posted

    Asalam Alaikum (Greetings)!

    i have a table in which i save data of services customer booked. when i run the query that which services is served by the expert to a customer, qurey like:

    Select A_Services.Service_Name as [Service Name],count(Service_Name) as [Total Serve]
    From A_Services
    Where FK_ExpertID = 2 group by Service_Name order by [Service Name]

    The Data Appears as:

    Service Name                        Total Serve
    ---------------------------------------------
    Furniture & Home Decor      1

    And i want a data like:

    Service Name                        Total Serve
    ---------------------------------------------
    Furniture & Home Decor      1
    Car                                         0
    Mobile                                   0
    Computer                              0

    and so on. Kindly help.

    Thank you,

    Regards,
    Muhammad Naveed Haroon

    Tuesday, February 6, 2018 10:27 AM

Answers

  • User452040443 posted

    Hi,

    Try:

    Select 
        A_Services.Service_Name as [Service Name],
        count(case when FK_ExpertID = 2 then Service_Name end) as [Total Serve] 
    From A_Services 
    group by 
        Service_Name 
    order by 
        [Service Name]

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 6, 2018 11:32 AM

All replies

  • User452040443 posted

    Hi,

    Try:

    Select 
        A_Services.Service_Name as [Service Name],
        count(case when FK_ExpertID = 2 then Service_Name end) as [Total Serve] 
    From A_Services 
    group by 
        Service_Name 
    order by 
        [Service Name]

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 6, 2018 11:32 AM
  • User992700523 posted

    Thank you sir!

    but i need one more thing. I have table which schema is like

    ServeID Furniture Laundry Water ISP Computer Tutor Mobile Car Gaming PA

    how can i get join from this. Your query shows me answers which i already used in table, like:

    Service Name  Total Serve
    Car Towing  0
    Furniture & Home Decor  1
    Home Tutor  0
    Personal Assistant  0

    How can i get remaining values such as computer, mobile etc. Kindly notice that Column name from schema is different such as Furniture and Data in Table is Furniture & Home Decor.

    Thank you,

    Regards,
    Muhammad Naveed Haroon

    Tuesday, February 6, 2018 11:50 AM
  • User452040443 posted

    Can you post sample data from the tables and their expected result?

    Tuesday, February 6, 2018 12:11 PM
  • User992700523 posted

    My first query is : 

    select COLUMN_NAME
    from Aasaani.INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = N'Serve' and ORDINAL_POSITION != 1
    
    COLUMN_NAME
    Furniture
    laundry
    Water
    ISP
    Computer
    Tutor
    Mobile
    Car
    Gaming
    PA

    Then i have a query:

    select * from A_Services where A_Services.FK_ExpertID = 2

    The answer of query is:

    SerivceID Service_Name Serivce_Charges FK_VendorID FK_ExpertID FK_CustomerID BookingDate TimeSlot current_Job GettingJobDate
    4 Furniture & Home Decor 200 1 2 1  08-11-2017 3 1 2018-02-04 13:57:49.733

    I want to count it with all the services. The answer i want as:

    Service Name Total Provide Services
    Furniture 1
    Laundry 0
    Water 0
    ISP 0
    Computer 0
    Tutor 0
    Mobile 0
    Car 0
    Gaming 0
    PA 0

    Thank you,

    Regards,
    Muhammad Naveed Haroon

    Tuesday, February 6, 2018 12:22 PM
  • User452040443 posted

    Try:

    with CTE_count as
    (
        Select 
            A_Services.Service_Name,
            count(case when FK_ExpertID = 2 then Service_Name end) as [Total Serve] 
        From A_Services 
        group by 
            Service_Name 
    )
    
    select
        coalesce(o.COLUMN_NAME, c.Service_Name) as [Service Name],
        c.[Total Serve]
    from CTE_count as c
    outer apply
    (
        select top(1)
            s.COLUMN_NAME
        from Aasaani.INFORMATION_SCHEMA.COLUMNS as s
        where 
            s.TABLE_NAME = N'Serve' and 
            s.ORDINAL_POSITION <> 1 and
            c.Service_Name like '%' + s.COLUMN_NANME + '%'
    ) as o
    order by 
        [Service Name]
    

    Hope this help

    Tuesday, February 6, 2018 12:52 PM
  • User992700523 posted

    Thank you,

    This is not returning the desire result. I t only shows limited data.

    Regards,
    Muhammad Naveed Haroon

    Tuesday, February 6, 2018 1:03 PM
  • User992700523 posted

    I put your query in this way:

    select COLUMN_NAME FROM AASAANI.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'Serve' and ORDINAL_POSITION != 1
    

    it returns:

    COLUMN_NAME
    Furniture
    Laundry
    Water
    ISP
    Computer
    Tutor
    Mobile
    Car
    Gaming
    PA

    Now how can i get value which is not in table such as Furniture is repeat once only. But the others is not. I run query :

    with MATCH as ( 
    select COLUMN_NAME FROM AASAANI.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'Serve' and ORDINAL_POSITION != 1
    )
    Select COLUMN_NAME,count(A_Services.Service_Name) as [Total]
    from A_Services inner join MATCH on A_Services.Service_Name like CONCAT('%',COLUMN_NAME,'%')
    where FK_ExpertID = 2
    group by COLUMN_NAME
    

    it returns result:

    COLUMN_NAME Total
    Furniture 1

    How can i get all the reamining such as CAR, PA, GAMING etc...

    Thank you,

    Regards,
    Muhammad Naveed Haroon

    Tuesday, February 6, 2018 1:16 PM
  • User452040443 posted

    Have you tested the query exactly as I posted?

    Tuesday, February 6, 2018 1:49 PM
  • User992700523 posted

    Yes but it get only those values which is present in table, not what i want. Like i get furniture, car and home tutor service in my table, so it get only these values not the values from the COLUMN_NAME schema.

    Tuesday, February 6, 2018 6:03 PM
  • User372437940 posted

    Check out the test done on the page below:

    http://sqlfiddle.com/#!18/9c8a7/2

    Hope this help

    Tuesday, February 6, 2018 6:58 PM
  • User992700523 posted

    Yes i got the same result but, the answer not contain other values such as laundry, gamin etc

    Wednesday, February 7, 2018 4:44 AM
  • User452040443 posted

    Try:

    with CTE_count as
    (
        Select 
            Service_Name,
            count(case when FK_ExpertID = 2 then Service_Name end) as [Total Serve] 
        From A_Services 
        group by 
            Service_Name 
    )
    
    select
        coalesce(s.COLUMN_NAME, c.Service_Name) as [Service Name],
        c.[Total Serve]
    from CTE_count as c
    full join Aasaani.INFORMATION_SCHEMA.COLUMNS as s 
        on c.Service_Name like '%' + s.COLUMN_NANME + '%'
    where 
        s.TABLE_NAME = N'Serve' and 
        s.ORDINAL_POSITION <> 1
    order by 
        [Service Name]

    Hope this help

    Wednesday, February 7, 2018 12:17 PM