Eliminating repeating rows from GROUP BY SQL Query

Question

Sorry if the title is a little bit confusing. But here's what I'm facing. I am running sql query below

SELECT kodnegeri.KodNegeriText, kategorisukan.KategoriSukanText, COUNT(*) AS Total
FROM association 
INNER JOIN kodnegeri ON association.KodNegeri = kodnegeri.KodNegeri 
INNER JOIN kategorisukan ON association.KodKategoriSukan = kategorisukan.KategoriSukan 
GROUP BY kodnegeri.KodNegeriText, kategorisukan.KategoriSukanText

And the query returns the following table

    KodNegeriText | KategoriSukanText | Total
    Johor         | Sukan Kecergasan  | 16
    Johor         | Sukan Paralimpik  | 1
    Johor         | Sukan Prestasi..  | 227
    Johor         | ...               | ...
    Kedah         | Sukan Kecergasan  | 14
    Kedah         | Sukan Paralimpik  | 8
    Kedah         | ...               | ...

As you can see under KodNegeriText, there are repeating rows (eg: Johor). How can I eliminate the repeating KodNegeriText items to obtain such result that only displays the KodNegeriText once.

Example.

KodNegeriText | KategoriSukanText | Total
Johor         | Sukan Kecergasan  | 16
              | Sukan Paralimpik  | 1
              | Sukan Prestasi..  | 227
              | ...               | ...
Kedah         | Sukan Kecergasan  | 14
              | ...               | ...

I have tried playing around with DISTINCT but failed to find the solution.

Are you sure you want to do this in sql? I'd do it in the report or form as it's presentation not data. If you do, we need to know which dbms, e.g. mysql, sql server etc. — Tony Hopkinson, Jan 12 '14 at 10:55
Rows 4 and 6 could be done in sql if ... is also, same as above — Tony Hopkinson, Jan 12 '14 at 10:57
Yes of course I've thought of that too. But just wondering if it's possible to do it directly from the query, it would be saving a lot of time. it's mysql by the way — mfmz, Jan 12 '14 at 10:57
Yes, it's possible you'd do it on the same basis as a running total query. It's complicated though, even more so if it involves more than just one column. The problem though, is it you reorder the output, or want to select a row from it and do something with it, you'll be in a right mess quick. — Tony Hopkinson, Jan 12 '14 at 10:59
Yeap it's the same. if '...' is the same as the above. For each KodNegeriText, there will be multiple KategoriSukanText. — mfmz, Jan 12 '14 at 11:00
Check this answer http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Francisco Puga, Jan 12 '14 at 11:01

score 0 · Answer 1 · answered Jan 12 '14 at 11:08

Your query is giving you the correct result. What you intend to do is to be achieved by storing resultset in temporary table and applying some tricks on SELECT list.

For SQL Server,

CREATE TABLE #temp(KodNegeriText varchar(100), KategoriSukanText varchar(100), Total int)

INSERT INTO #temp
SELECT kodnegeri.KodNegeriText, kategorisukan.KategoriSukanText, COUNT(*) AS Total,
FROM association 
INNER JOIN kodnegeri ON association.KodNegeri = kodnegeri.KodNegeri 
INNER JOIN kategorisukan ON association.KodKategoriSukan = kategorisukan.KategoriSukan 
GROUP BY kodnegeri.KodNegeriText, kategorisukan.KategoriSukanText


;WITH tempCTE AS
(

SELECT KodNegeriText , KategoriSukanText , Total ,
       Row_Number() OVER(PARTITION BY KodNegeriText ORDER BY KategoriSukanText,Total) as Rownum
FROM #temp

)

SELECT CASE WHEN Rownum=1 THEN  KodNegeriText ELSE '' END as KodNegeriText,KategoriSukanText , Total
FROM tempCTE 


drop table #temp

Eliminating repeating rows from GROUP BY SQL Query

1 Answers1