How to group the results by country in SQL or Report builder-CodePudding

Good day to you.

I have a question.. i am using report builder to build some reports. And i got stuck in this place where i want to sum all the results of one month to be in one cell.

For example, in this picture you see that under the country1 i have 2 "1" i want them to be in one line and the sum/total will be 2

I tried to group by country... but it didn't make any difference in the view.

How can i do that? Is there a way to do it via the report builder or through my SQL query?

Thank you

NOTE: I used this code to get the days field in the report builder

=iif(Fields!Date_WEEKDAY__NUMBER_.Value=1,"Sunday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=2,"Monday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=3,"Tuesday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=4,"Wednesday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=5,"Thrusday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=6,"Friday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=7,"Saturday","Null")))))))

And this is my main quarry in SQL:

SELECT COUNT([Patient ID]) AS ID, Activity,Date, [Interaction Type], [Adverse Event Occured], [Product Compaint Occured], [Cancelation Reason], Created, Status, [Interaction Reason], Country, Drug, 
              [Registered Nurse], [Created by], YEAR(Date) AS [Date Year], 
              DATENAME(MONTH, Date) AS [Date Month], 
              DATENAME(WEEK, Date) AS [Date WEEK], 
              DATENAME(WEEKDAY, Date) AS [Date WEEKDAY],
              DATEPART(WEEKDAY, Date) AS [Date WEEKDAY (NUMBER)]
FROM     MNZL_Patient_Activities
GROUP BY ID, Activity, Date, [Interaction Type], [Adverse Event 
Occured], [Product Compaint Occured], [Cancelation Reason], 
Created, Status, [Interaction Reason], Country, Drug, [Registered 
Nurse], [Created by], YEAR(Date), 
              DATENAME(MONTH, Date), DATENAME(WEEK, Date), 
DATENAME(WEEKDAY, Date)
HAVING ([Interaction Reason] = N'Medication Administration')
order by DATENAME(WEEK, Date)

CodePudding user response：

This Query gonna sums all the ones in a "country line" and gives you back the total under a column named total The "ISNULL" command is to avoid some trubles due to the sum between 1 "NULL" It substitutes the values null if it's present with zero

SELECT  MT.[YEAR],
        MT.[ENROLMENT DATE],
        MT.[ENROLMENT DATE WEEK],
        MT.[COUNTRY],
        ISNULL(MT.SUNDAY,0) 
        ISNULL(MT.MONDAY,0) 
        ISNULL(MT.TUESDAY,0) 
        ISNULL(MT.WEDNESDAY,0) 
        ISNULL(MT.THRUSDAY,0) 
        ISNULL(MT.FRIDAY,0) 
        ISNULL(MT.SATURDAY,0)   AS TOTAL
FROM    MyTable MT

CodePudding user response：

I got it.. it was a silly mistake missed by me.. i didnt delete the "Details" grouping.. and it was grouping IDs Causing each ID to go in separate row... Once i removed it it grouped IDs based on the month (The desired result)

Feeling extra stupid right now :)

Anyways, thank you very much everyone <3