You must Sign In to post a response.
  • Category: SQL Server

    Query to get count by data

    Hello. In microsoft sql server,
    I have 3 tables, "Hotel", "Mood" and "Feedback
    Hotel :- Id, Name
    Mood :- Id, Name
    Feedback : Id, HotelId, MoodId, FeedbackText

    In Hotel, there are 2 records. (Hotel1, Hotel2)
    In Mood, there are 3 records (Happy, Unhappy, Neutral)
    Feedback is actual transaction table.
    So, I want out put like,

    Headers :- Hotel, Happy, Unhappy, Neutral
    Record1 :- Hotel1, 12, 5, 2
    Record1 :- Hotel2, 22, 3, 1

    Please help me to write this query.
  • #768854
    Hi,

    In those tables Hotel and Mood their should be a Primary Key right and that should be the foreign key for the other table.
    I mean we have Hotel column as Hotel ID and Hotel Name and in the MOOD table we have MOOD ID and MOOD TYPE, how you are connecting this two tables into one?
    This two table don't have any relation
    Hotel ---- MOOD

    Thanks,
    Mani

  • #768855
    There is no relation between Hotel and Mood.
    There are 2 different entities while giving feedback.

  • #768856
    Hi,

    Hotel and Mood both are parent tables and Feedback is child table right?

    Now, you want to get the records based on child table and contact with parents.

    Ex:


    select H.Name, M.Name
    from Hotel H,
    Mood M,
    Feedback fb
    where fb.HotelId=H.Id And fb.MoodId=M.Id


    Now, You need to change rows to columns like headers for Mood table like Happy , UnHappy, Neutral.. For that you have to use PIVOT Table using that technique you can easily change Rows to column using above result.

    Hope this helps you....

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768872
    Hai Pranjal,
    You can use GroupBy clause by the Hotel name to get the count of records. The Mood table should have Group By hotel to get the count of the records and then you can join it with the first table to get the total records.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #769020
    SELECT *
    FROM
    (SELECT H.HotelName,M.MoodName
    From Feedback F LEFT JOIN Hotel H ON H.Id=F.HotelID
    LEFT JOIN Mood M ON M.Id=F.MoodID
    )P
    PIVOT
    (
    COUNT(MoodName)
    FOR MoodName IN ([Happy], [Unhappy], [Neutral])
    )AS PVT
    We can achieve your requirement using pivot concept in sql


Sign In to post your comments