Hello everyone, I need your help.
This is my table in db mysql:
id DR DATE HOUR ST IM SB
48 TOU 2010-02-05 21:04 D_A DX00-1-380189 RO
49 TOU 2010-02-05 21:04 D_A DX00-1-380189 RO
105 TOU 2010-03-10 08:51 D_A DX00-1-380110 RO
133 TOU 2010-03-30 23:10 D_A DX00-1-400115 AA
156 TOU 2010-04-19 18:53 D_A DX00-1-400113 AA
156 TOU 2010-04-19 18:53 D_B DX00-1-400123 FF
And this is my query:
SELECT DR
, COUNT(CASE WHEN st = 'D_A'
THEN st END) AS strD_A
, COUNT(CASE WHEN stato = 'D_B'
THEN st END) AS strD_B
, COUNT(CASE WHEN st = 'D_C'
THEN st END) AS strD_C
, COUNT(CASE WHEN st = 'D_D'
THEN st END) AS strD_D
, COUNT(*) AS tot
FROM tbl_m
GROUP BY DR
And this is the output:
DR strD_A strD_B strD_C strD_D tot
TOU 5 1 0 0 6
I need this output, any help would be very much appreciated.
Many thanks to any who can help me out with this:
First a question: is it ok to have duplicate records in your table? If not, get rid of them
Possible solution to your problem:
SELECT
DR
, COUNT(CASE WHEN st = 'D_A'
THEN st END) AS strD_A
, COUNT(CASE WHEN stato = 'D_B'
THEN st END) AS strD_B
, COUNT(CASE WHEN st = 'D_C'
THEN st END) AS strD_C
, COUNT(CASE WHEN st = 'D_D'
THEN st END) AS strD_D
, COUNT(*) AS tot
FROM
(SELECT DISTINCT *
FROM tbl_m
) AS a
GROUP BY DR
O yeah, that’s because of the id. The two rows aren’t identical in all fields. Just substitute the ‘*’ in the subquery with the fields of the row that you want to be unique.
I don’t mean to be rude, but why would you want to get incorrect results? You’re getting exactly what you should be.
ST_A. I count five rows here…
id DR DATE HOUR ST IM SB
48 TOU 2010-02-05 21:04 D_A DX00-1-380189 RO
49 TOU 2010-02-05 21:04 D_A DX00-1-380189 RO
105 TOU 2010-03-10 08:51 D_A DX00-1-380110 RO
133 TOU 2010-03-30 23:10 D_A DX00-1-400115 AA
156 TOU 2010-04-19 18:53 D_A DX00-1-400113 AA
SELECT
DR
, COUNT(CASE WHEN st = 'D_A'
THEN st END) AS strD_A
, COUNT(CASE WHEN st = 'D_B'
THEN st END) AS strD_B
, COUNT(CASE WHEN st = 'D_C'
THEN st END) AS strD_C
, COUNT(CASE WHEN st = 'D_D'
THEN st END) AS strD_D
, COUNT(*) AS tot
FROM
(SELECT DISTINCT DR
FROM tbl_m
) AS a
GROUP BY DR
SELECT
DR
, COUNT(CASE WHEN st = 'D_A'
THEN st END) AS strD_A
, COUNT(CASE WHEN stato = 'D_B'
THEN st END) AS strD_B
, COUNT(CASE WHEN st = 'D_C'
THEN st END) AS strD_C
, COUNT(CASE WHEN st = 'D_D'
THEN st END) AS strD_D
, COUNT(*) AS tot
FROM
(SELECT DISTINCT DR, DATE, HOUR, ST, IM, SB
FROM tbl_m
) AS a
GROUP BY DR
You’ll have to put DATE and HOUR between backticks, as they are reserved words.