SQL Calculations based by field type and group by the type

Question

Database includes FamID, TicketType and Amt I want to get a calculation for total amount for each tickettype for each family and sort by family high to low based on total for all tickettypes.

Database values are:

FamID           TicketType             Amt
1                1                     10
1                1                     10
1                2                     20
1                3                     30
2                2                     20
2                1                     10
2                1                     10
2                1                     10
2                3                     30
3                3                     30
3                3                     30
3                3                     30

Would like results to be

Family         Type 1         Type 2        Type 3         Total
3                0               0             90            90
2                30              20            30            80
1                20              20            30            70

Am I trying to do too much?

What is your RDBMS? Does it support `PIVOT`? Do you know all posiible values of `TicketType` upfront? — PM 77-1, Feb 11 '16 at 01:48

Tim Biegeleisen · Accepted Answer · 2016-02-11T14:42:40.420

2

You never specified your RDBMS, but the following pivot query should work across most major ones with little modification:

SELECT t.`Type 1`, t.`Type 2`, t.`Type 3`,
    (t.`Type 1` + t.`Type 2` + 2*t.`Type 3`) AS Total
FROM
(
    SELECT FamID AS Family,
        SUM(CASE WHEN TicketType = 1 THEN Amt ELSE 0 END) AS `Type 1`,
        SUM(CASE WHEN TicketType = 2 THEN Amt ELSE 0 END) AS `Type 2`,
        SUM(CASE WHEN TicketType = 3 THEN Amt ELSE 0 END) AS `Type 3`,
    FROM Tickets
    GROUP BY FamID
) t
ORDER BY t.Total DESC

edited Feb 11 '16 at 14:42

answered Feb 11 '16 at 01:50

Tim Biegeleisen

502,043
27
286
360

Thank you so much. This worked perfect. I am using MS SQL. Appreciate the quick feedback. – Kathy Kaiser Minnick Feb 11 '16 at 14:03
Sorry Tim - never used this before so wasn't sure on the process. I have an extension of this question - should I ask here or start a new question? – Kathy Kaiser Minnick Feb 11 '16 at 14:29
You can let me know what the extension is. If it be small, I will answer here. If large, then yes you should ask a new question and reference this one, and feel free to take my query to use. – Tim Biegeleisen Feb 11 '16 at 14:30
In this case the total should be Type1+Type2+(Type3*2) - I can get Type3 to double by adding *2 after that Amt - but how do I do it in the total? It makes a difference in the order by to show high to low totals. – Kathy Kaiser Minnick Feb 11 '16 at 14:36
@KathyKaiserLees I have updated the query to meet your new specifications. I hope it helps you. – Tim Biegeleisen Feb 11 '16 at 14:43
1

Thank you! Have a great day – Kathy Kaiser Minnick Feb 11 '16 at 14:45
Tim - the new query doesn't work. This is what I have. Select t.'Type1', t.'Type2', t.'Type3', t.'Type5', t.'Type4',(t.'Type1' + t.'Type2' + t.'Type3' + t.'Type4' + t.'Type5') as Total FROM ( SELECT FamID as Family, SUM(CASE WHEN TicketType =1 THEN Amt ELSE 0 END) AS `Type1`, SUM(CASE WHEN TicketType =2 THEN Amt ELSE 0 END) AS `Type2`, SUM(CASE WHEN TicketType = 3 THEN Amt ELSE 0 END) AS `Type3`, SUM(CASE WHEN TicketType =4 THEN Amt ELSE 0 END) AS `Type4`, SUM(CASE WHEN TicketType = 5 THEN Amt ELSE 0 END) AS `Type5' FROM Passes1 GROUP BY FamID) t ORDER BY t.'Total' DESC – Kathy Kaiser Minnick Feb 11 '16 at 17:39
No error - just no results...Let me see how to turn on error reporting - never used it before... – Kathy Kaiser Minnick Feb 11 '16 at 18:00
I get You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Type1', t.'Type2', t.'Type3', t.'Type5', t.'Type4',(t.'Type1' + t.'Type2' + t.'' at line 1 – Kathy Kaiser Minnick Feb 11 '16 at 18:04
You need to use backticks when referring to the column names in the outer query like this: `SELECT t.\`Type 1\` ...` You are using single quotes like this: `SELECT t.'Type 1' ...` The query is fine and of course you must adjust it to your own environment. – Tim Biegeleisen Feb 11 '16 at 18:12
That did it.. Thank you. – Kathy Kaiser Minnick Feb 11 '16 at 18:26

SQL Calculations based by field type and group by the type

1 Answers1