0

I have a table which keeps the record for letter send by different department to different region(A,B,C) in Hindi or English language as given in the table.

Tblltr image

I want to count the number of letter send by Each Department to Region A,B,C in Hindi And English as per the result table.

Result table

I am able to get this for all department together but not able to do is as per Department. Kindly guide me get that result.my query for all record. I tried cross join alias but not able to get it in given format.I tried multiple answer given here but no joy.

select count(LtrNo) from tblltr where REGION="A" and Language="Hindi" 
select count(LtrNo) from tblltr where REGION="A"and Language="English"
select count(LtrNo) from tblltr where REGION="B" and Language="Hindi"
select count(LtrNo) from tblltr where REGION="B" and Language="English"
select count(LtrNo) from tblltr where REGION="C" and Language="Hindi"
select count(LtrNo) from tblltr where REGION="C" and Language="English"

Fiddle here Fiddle

leraner
  • 71
  • 6

1 Answers1

2

Use a condition in SUM() to get the count of rows matching that condition, and then group by department and region. Use WITH ROLLUP to add subtotals.

SELECT IFNULL(department, 'Total') AS Department, 
    IFNULL(region, 'Total') AS Region, 
    COUNT(*) AS Total,
    SUM(language = 'Hindi') AS Hindi,
    SUM(language = 'English') AS English
FROM tblltr
GROUP BY department, region
WITH ROLLUP
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks for your answer. Is it possible to write the department once (like merged cell)instead of multiple times – leraner Apr 27 '23 at 17:14
  • That's something you should do in your display application, it can't be done easily in SQL. – Barmar Apr 27 '23 at 17:17
  • See https://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 for a PHP example. – Barmar Apr 27 '23 at 17:17
  • THanks your answered works for me I was not having any idea about rollup – leraner Apr 27 '23 at 17:23