CODEWITHSUNDEEP
mysqlview
flukyspore
flukyspore

Reputation: 696

Creating a VIEW in mysql

I would like to create a view from the following table. This table contains many many records and my goal is to have a view hold certain TOTALS (sums) for Column A and Column B but based on different WHERE clauses. Currently I have created two separate views to keep track of the sums that I need.

My question is if I can do this in one view as opposed to two separate views.

TABLE: USERID TIME COLUMN A COLUMN B

I want to keep track of the following two totals:

TOTAL 1: SELECT USERID, SUM(TIME) FROM TABLE WHERE COLUMN A <> 0
TOTAL 2: SELECT USERID, SUM(TIME) FROM TABLE WHERE COLUMN B <> 0

Is there a way to get these 2 totals in one query?

Upvotes: 1

Views: 156

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

SELECT USERID,
       SUM(CASE WHEN COLUMN_A<>0 THEN TIME ELSE 0 END) AS ColumnASum,
       SUM(CASE WHEN COLUMN_B<>0 THEN TIME ELSE 0 END) AS ColumnBSum
    FROM TABLE
    GROUP BY USERID

Upvotes: 2

Matt Dodge
Matt Dodge

Reputation: 11142

Views in MySQL cannot contain multiple queries but it doesn't sound like you necessarily need a view. Why not just do

SELECT USERID, SUM(TIME) FROM TABLE WHERE COLUMN A <> 0
UNION
SELECT USERID, SUM(TIME) FROM TABLE WHERE COLUMN B <> 0

Is that not giving you what you need?

Upvotes: 0

Related Questions