SQL to Sum columns in a row

Question

I have an sql that gives me this data:

    Dim MySqlString As String = "SELECT USERID, "
    MySqlString += "SUM(case when ACTVCODE = 'POC' then 1 else 0 end) POC, "
    MySqlString += "SUM(case when ACTVCODE = 'PIC' then 1 else 0 end) PIC, "
    MySqlString += "SUM(case when ACTVCODE = 'EML' then 1 else 0 end) EML, "
    MySqlString += "SUM(case when ACTVCODE = 'AD' then 1 else 0 end) AD, "
    MySqlString += "SUM(case when RESULTCODE = 'LOG' then 1 else 0 end) LOG, "
    MySqlString += "SUM(case when RESULTCODE = 'SR' then 1 else 0 end) SR, "
    MySqlString += "SUM(case when RESULTCODE = 'DM' then 1 else 0 end) DM, "
    MySqlString += "SUM(case when RESULTCODE = 'GK' then 1 else 0 end) GK, "
    MySqlString += "SUM(case when RESULTCODE = 'AM' then 1 else 0 end) AM, "
    MySqlString += "SUM(case when RESULTCODE = 'NA' then 1 else 0 end) NA, "
    MySqlString += "SUM(case when RESULTCODE = 'RO' then 1 else 0 end) RO, "
    MySqlString += "SUM(case when RESULTCODE = 'ORE' then 1 else 0 end) ORE, "
    MySqlString += "SUM(case when RESULTCODE = 'COM' then 1 else 0 end) COM, "
    MySqlString += "SUM(case when RESULTCODE = 'SMS' then 1 else 0 end) SMS, "
    MySqlString += "SUM(case when RESULTCODE = 'SV' then 1 else 0 end) SV, "
    MySqlString += "SUM(case when RESULTCODE = 'QUO' then 1 else 0 end) QUO, "
    MySqlString += "SUM(case when RESULTCODE = 'PQU' then 1 else 0 end) PQU "

I am trying to add a column at the end that will sum up each row. I tried using SUM(POC + PIC + EML + AD + LOG) but that did not work the way I thought it would. Any suggestions?

Just use basic Math a+b+c+d+e=Total or you could use a [computed column](https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx) to have the value "stored" so you can retrieve it w/o the overhead when called. Overhead is on save. https://stackoverflow.com/questions/8038221/creating-a-computed-column-in-sql-server-2008 — xQbert, Oct 09 '17 at 19:36
Why store numerical data in varchar columns? Use proper integer data type instead. — jarlh, Oct 09 '17 at 19:54
They are int's it's actually giving me an error saying these are invalid column names — KMurray, Oct 09 '17 at 19:55

score 6 · Accepted Answer · answered Oct 09 '17 at 19:33

6

You don't need to SUM:

POC + PIC + EML + AD + LOG

answered Oct 09 '17 at 19:33

Amit

45,440
9
78
110

3

Just to note, if any of the columns are `NULL` then the sum will be `NULL`. – justiceorjustus Oct 09 '17 at 19:35
1

`coalesce(poc,0) + coalesce(pic,0)... as RowTotal` – xQbert Oct 09 '17 at 19:35
You can also use `ISNULL(poc,0)` – Venkataraman R Oct 09 '17 at 19:39
It's giving me this error: Conversion failed when converting the varchar value 'POC' to data type int – KMurray Oct 09 '17 at 19:45
Did you use `isnull(POC, 0) + isnull(PIC, 0) + ...` ? What type is POC? – Oct 09 '17 at 19:47
@KMurray - that's why you need to ask better questions. Anyway, you first need to cast your varchar to an int (or better - fix your schema). – Amit Oct 09 '17 at 19:48
1

@VenkataramanR, `COALESCE()` is ANSI SQL, while `ISNULL` is product specific - and no dbms is specified here. – jarlh Oct 09 '17 at 19:53
It seems table header added as a row – Serkan Arslan Oct 09 '17 at 19:55
@jarlh, Got it. You are right. I did not check the RDMBS. – Venkataraman R Oct 09 '17 at 20:55

SQL to Sum columns in a row

1 Answers1