How i can SELECT with multiple WHERE in one table

244
July 06, 2017, at 04:05 AM

i have table in database with rows:

id, kills, deaths, week

How i can select "kills" where week=1 AND deaths where week=2 in ONE TABLE?

Answer 1

You can use conditional aggregated

SELECT SUM (CASE WHEN week = 1 THEN kills END) as total_kills_week1, 
       SUM (CASE WHEN week = 2 THEN deaths END) as total_deaths_week2
FROM YourTable 
Answer 2

You can use two seperate select statements:

SELECT(
    (SELECT kills FROM tablename WHERE week = 1),
    (SELECT deaths FROM tablename WHERE week = 2)
)

This will create a column for kills where week = 1, and another column for deaths where week = 2.

You can use the UNION keyword to combine them together:

SELECT 'week 1 - kills' as source, kills FROM tablename WHERE week = 1
UNION
SELECT 'week 2 - deaths' as source, deaths FROM tablename WHERE week = 2

These two approaches require double scanning the tables.

An alternate approach is to use a condition clause @Juan Carlos Oropeza's solution if you need an aggregate function. However, as pointed out, it may not be the best idea to combine these two statements into one.

Answer 3

Although what @yelq said is true, but one should not prefer such a thing.
The primary purpose of query calls is to display related data. In your case you are asking to display unrelated data in fields in the same table.
It is preferred in this case to make two different queries to display data.

Rent Charter Buses Company
READ ALSO
Excel Equivalent of SQL query “SELECT A.one, B.two FROM A INNER JOIN B ON A.three = B.three”

Excel Equivalent of SQL query “SELECT A.one, B.two FROM A INNER JOIN B ON A.three = B.three”

How do you do SQL "select join on" in excel? Say I want col one from table A, col two from table B and they both have col three

283
SQL - counts and sum for different values of count and name

SQL - counts and sum for different values of count and name

I have a table with results from various tournaments (only showing the relevant fields for this case):

296
WAMP MySQL will not start now after having unsolved password issues

WAMP MySQL will not start now after having unsolved password issues

First I want to mention that the original issue was password relatedI kept getting wrong password error in both phpmyadmin and mysql console

245