Celcius_Bitz Celcius_Bitz - 1 month ago 6
C# Question

Sql - merge two query to make it rows

i want to get the disease total and last week total from this table(tb_data):

P_ID Disease Date
1 A 2016-10-11
2 A 2016-10-11
3 A 2016-10-14
4 A 2016-10-19


while i have query like this:

SELECT Disease AS DT,
COUNT(P_ID) AS PT
FROM tb_data
GROUP BY Disease
union
SELECT Disease AS DLW,
COUNT(P_ID) AS PLW
FROM tb_data
WHERE Date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND Date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
GROUP BY Disease
ORDER BY 2 DESC, 1;


what i want as a result is like this

DT PT DLW PLW
A 4 A 3


not this

DT PT
A 4
A 3


because i want my program read this:

foreach (DataRow kolom in table.Rows)
{
Total = kolom["PT"].ToString();
Lastweek = kolom["PLW"].ToString();
}
labelTotal.Text = Total;
labelLastWeek.Text = Lastweek;


thx in advance

Answer

I think conditional aggregation is what you need.

select disease as dt,
         sum(1) as pt,
         disease as dlw,
         sum(case when date between curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY and curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY then 1 else 0 end) as plw
from    tb_data
group   by disease;

Result

+------+------+------+------+
| dt   | pt   | dlw  | plw  |
+------+------+------+------+
| A    |    4 | A    |    3 |
+------+------+------+------+