CODEWITHSUNDEEP
sqlsql-server-2008highcharts
emerson.marini
emerson.marini

Reputation: 9348

SQL query for a graphic chart. Not sure about how to use JOIN in this case

I'm trying to write an SQL query to fulfill a graphic chart, and am not sure how to do it properly. Here's the situation. The chart type is bar, where I want to list the 'Message Types' on the Y Axis, the 'Number of Messages' on the X Axis, and for each value on the Y Axis, create a stacked bar with the number of 'Messages' for each user in a 'Message Type'. Something like this: http://www.highcharts.com/demo/bar-stacked

What I want to return is:

SELECT COUNT(Messages.MessageId), Messages.UserName, MessageTypes.Description

It's really simple, but my issue is that I want to have all the possible 'MessageTypes' on the Y Axis, even if there are no 'Messages' at all linked to them (bring 0 as the COUNT in this case).

I've tried this, but it's not working:

FROM MessageTypes 
LEFT OUTER JOIN Messages ON MessageTypes.MessageTypeId = Messages.MessageTypeId

Any ideas?

Thanks in advance for any help provided.

Upvotes: 1

Views: 101

Answers (1)

Moho
Moho

Reputation: 16498

edit: you changed the question on me! ;)

group by description and user.name, count records, and left outer join b/t Messages and Users:

select
    mt.Description MessageTypeDescription
    , m.UserName -- will be null if no messages exist for a given type
    ,  UserMessageTypeCount = case when m.UserName is null then 0 else COUNT(1) end
from
    MessageTypes mt
    left outer join Messages m
     on mt.MessageTypeId = m.MessageTypeId
group by
    mt.Description
    m.UserName

The results will be the number of messages per message type per user

Upvotes: 1

Related Questions