CODEWITHSUNDEEP
sql
Marwan Salim
Marwan Salim

Reputation: 722

Combine rows in same column and table with similar values and sum values

I have very large data set as below (example) :

#ID  #report_name           #report_count 
1     ReportA1              3
2     ReportA1(PDF)         4
3     ReportA2              2
4     ReportA2(PDF)         6
5     ReportA3              7
6     ReportA3(PDF)         2
7     ReportA4              9   
8     ReportA4(PDF)         7
9     ReportA5              1
10    ReportA5(PDF)         2
11    ReportA6              8
12    ReportA6(PDF)         9
13    ReportA7              8
14    ReportA7(PDF)         6
15    ReportA8              6
16    ReportA8(PDF)         7
17    ReportA9              5
18    ReportA9(PDF)         9
19    ReportA10             7
20    ReportA10(PDF)        1

I want to make SQL query so that i can merge two rows as single rows.

For example ReportA1 and ReportA1(PDF) as ReportA1.

My Question, how to create SQL statement to combine two rows of data (i.e ReportA1 and ReportA1(PDF)) and count it as single name (i.e ReportA1) as table below (example only) ?

#ID  #report_name   #report_count       
1   ReportA1        7
2   ReportA2        8
3   ReportA3        9
4   ReportA4        16
5   ReportA5        3
6   ReportA6        17
7   ReportA7        14
8   ReportA8        13
9   ReportA9        14
10  ReportA10       8

Upvotes: 2

Views: 170

Answers (5)

sameh.q
sameh.q

Reputation: 1709

Example that would help you:

SELECT
    report_name,
    report_count
FROM
(
    SELECT REPLACE(report_name, '(PDF)') AS report_name, SUM(report_count) AS report_count FROM
    (
        SELECT 'RPT A1(PDF)' AS report_name, 4 AS report_count FROM DUAL
         UNION
        SELECT 'RPT A1' AS report_name, 3 AS report_count FROM DUAL
         UNION
        SELECT 'RPT A2(PDF)' AS report_name, 12 AS report_count FROM DUAL
         UNION
        SELECT 'RPT A2' AS report_name, 5 AS report_count FROM DUAL
         UNION
        SELECT 'RPT A3(PDF)' AS report_name, 8 AS report_count FROM DUAL
         UNION
        SELECT 'RPT A3' AS report_name, 2 AS report_count FROM DUAL
    )
    GROUP BY REPLACE(report_name, '(PDF)')
)
ORDER BY report_name

This example output is:

RPT A1  7
RPT A2  17
RPT A3  10

Summary:

using Group by GROUP BY REPLACE(report_name, '(PDF)') will remove the word (PDF) from the report_name column and group by the result of the REPLACE function

Upvotes: 0

Edper
Edper

Reputation: 9322

In mySQL you can probably do this:

SELECT ReportName, SUM(ReportCount) as ReportsCount 
FROM (
  SELECT REPLACE(ReportName,'(PDF)','') as ReportName, ReportCount
  FROM Reports ) T
GROUP BY ReportName

See Demo

Upvotes: 2

Akhil R J
Akhil R J

Reputation: 184

In Sql Server:

SELECT RName AS 'report_name', SUM(report_count) AS 'report_count'
FROM(
    SELECT REPLACE(report_name,'(PDF)','') AS 'RName' 
           ,report_count 
    FROM dbo.test)
GROUP BY [RName]

This should work

Upvotes: 0

Nirman
Nirman

Reputation: 6783

I consider report_name field always differ by "(pdf)" word in each two rows. If that's the case then you can try out following:

select ROW_NUMBER() OVER(Order by replace(report_name, '(pdf)', '')) as ID, 
replace(report_name, '(pdf)', '') as report_name, sum(report_count) as report_count
FRom @temp
Group by replace(report_name, '(pdf)', '')

Upvotes: 0

tomas
tomas

Reputation: 840

This should help you

select substr(report_name,8),sum(report_count) from yourtablename
groupb by (substr(report_name,8))

This is Oracle Syntax, but every SQL dialekt have a "substr" methode.

Upvotes: 0

Related Questions