SQL problems merging queries

[Stevenn]

I print data from an Oracle database with adodb in Excel 2007.

I have problems merging two sql queries. Now I use

SELECT AMOUNT FROM TABLE_NAME WHERE BUDGET_ID = 1

and

SELECT AMOUNT FROM TABLE_NAME WHERE BUDGET_ID = 20

and want to merge these, so the recordset contains two columns "amount_1" and "amount_24". I guess I need to make two queries and naming these somehow.

 

[Firefly2012]

Hi

Are they both targeting the same table? If so, wouldn't you want to combine them like:

SELECT BUDGET_ID,AMOUNT FROM TABLE_NAME WHERE BUDGET_ID In(1,20)

You can easily extend the In() to cover other Budet_IDs as appropriate.

If you really want to do it the way you have perhaps:

SELECT Amount As [Amount1], '' As [Amount20] FROM Table_Name WHERE Budget_ID = 1
 
UNION ALL
 
SELECT '' As [Amount1], Amount As [Amount20] FROM Table_Name WHERE BudgetID=20

 

[Colin Legg]

I'm not familar with Oracle but I here's what I'd write if I were querying a SQL Server database. I assume the syntax is going to be similar or identical in this case:

SELECT CASE WHEN Budget_ID = 1 THEN Amount ELSE 0 END AS Amount_1,
       CASE WHEN Budget_ID = 20 THEN Amount ELSE 0 END AS Amount_24
FROM TABLE_NAME
WHERE Budget_ID IN (1,20)

 

[Stevenn]

Thank you very much for the quick answers.

I guess I should've written how my data look like.

==============
Id, Project_number, Amount, Budget_id
-----------------------------
1, 1, 312, 1
2, 1, 421, 1
3, 1, 900, 20
4, 1, 812, 20
==============

I want to combine these rows to just one row because the project number is the same.

I guess it's something like

SELECT SUM(Amount) As Amount FROM Table_name GROUP BY Project_number

. The problem is to split the two sums "sum of amount with budget_id=1" and "sum of amount with budget_id = 20" in two columns.

I hope it was clear. It was really not good explained in my previous post.

 

[Colin Legg]

SELECT Project_Number,
    SUM(CASE WHEN Budget_ID = 1 THEN Amount ELSE 0 END) AS Amount_1,
    SUM(CASE WHEN Budget_ID = 20 THEN Amount ELSE 0 END) AS Amount_24 
FROM TABLE_NAME
WHERE Budget_ID IN (1,20)
GROUP BY Project_Number

 

[Stevenn]

SELECT Project_Number,
   SUM(CASE WHEN Budget_ID = 1 THEN Amount ELSE 0 END) AS Amount_1,
   SUM(CASE WHEN Budget_ID = 20 THEN Amount ELSE 0 END) AS Amount_24 
FROM TABLE_NAME
WHERE Budget_ID IN (1,20)
GROUP BY Project_Number

Thank you! It works just perfect. I am trying to make a Amount_1 and Amount_24 for each month. With my previous code I was selecting

SUM(CASE WHEN MONTH = 12 THEN AMOUNT ELSE 0 END) AS JANUARY

I have troubles combining those two codes. Is it by the way possible to write the code smarter since it will give 'cost' me 24 lines for just the amount_1 and amount_24 for each month

 

[Stevenn]

It seems a bit stupid to write

SUM(CASE WHEN BUDGET_ID = 0 AND MONTH = 1 THEN AMOUNT ELSE 0 END) AS AMOUNT_0_JANUARY

for each month and each budget_id

 

[Colin Legg]

I'm sure I can cobble something together for you (again, based on SQL Server). If we're clever then I think we'll be able to find a way of pivoting it.

Which column from the table is giving you the month? I assume you don't have a column called Month, do you? (that wouldn't be great, since Month is the also name of a function).

 

[Stevenn]

I'm sure I can cobble something together for you (again, based on SQL Server). If we're clever then I think we'll be able to find a way of pivoting it.

Which column from the table is giving you the month? I assume you don't have a column called Month, do you? (that wouldn't be great, since Month is the also name of a function).

I guess it will be a problem to use pivot tables because I'm going to upload the data after I've retrieved the data.

I've got a field called MonthNumber for each row. I know I can write these 12*4 lines with a loop, but I guess it's not the best way. Maybe a loop in SQL?

 

[Firefly2012]

Do you mean like this (using Colin's SQL):

SELECT Project_Number, MonthNumber,
   SUM(CASE WHEN Budget_ID = 1 THEN Amount ELSE 0 END) AS Amount_1,
   SUM(CASE WHEN Budget_ID = 20 THEN Amount ELSE 0 END) AS Amount_24 
FROM TABLE_NAME
WHERE Budget_ID IN (1,20)
GROUP BY Project_Number, MonthNumber