Category: SQL Server

09 Feb 2017 15:04

Joined:	11/12/2010
Level:	Silver
Points:	53

Points:	4

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

09 Feb 2017 15:13

Joined:	18/12/2008
Level:	Gold
Points:	2921

Points:	3

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

09 Feb 2017 15:15

Joined:	11/12/2010
Level:	Silver
Points:	53

Points:	1

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

#768856

09 Feb 2017 18:24

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

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

13 Feb 2017 15:01

Joined:	28/04/2006
Level:	Gold
Points:	29723

Points:	2

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

08 Mar 2017 17:54

Joined:	16/08/2007
Level:	Silver
Points:	248

Points:	3

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

Query to get count by data

About dotnetspider.com

Quick Links

General

Services