Problem with GROUP BY

Databases
1

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:


DR	strD_A	strD_B	strD_C	strD_D	tot
TOU	4	1	0	0	5

Because the ID 48 and 49 it’s the same record…

Thanks in advance.
Chevy

2

First a question: is it ok to have duplicate records in your table? If not, get rid of them :slight_smile:

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
3

Thansk for your help, but whit your query the output not change…

4

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.

5

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

ST_B, I count one here…


156    TOU    2010-04-19    18:53    D_B     DX00-1-400123    FF
6

Not change nothing…
I need count 1 this rows, it’s not possible?:

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
7

What didn’t change anything? Did you change your query? Please post it here again.

8 
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

Unknown colum st in field list

9 

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.

10

Well this query surely didn’t give you the same result as the one with the asterisc. This one gives an error :slight_smile:

11

thanks Guido! :slight_smile: