SQL Query

[Serigraphie]

Hi Everyone,

a good day to all of you. I am a newbie in SQL queries and I would like to ask a question in an existing query I have.



SELECT
SUM (SPEND) SPEND1,
FROM
TIME
WHERE LTM = 1

Now I need to modify this query to add another column called SPEND2 but this time, it's where clause shouldn't be LTM=1, it should be L4M = 1

I can't do LTM=1 and L4M = 1 because the where clause will apply to both SPEND1 and SPEND2.
Spend1 where should be LTM=1 and Spend2 should be L4M=1

they should be in the same query.

I would like to do this :

Select
Sum (Spend) as Spend1 where LTM = 1
Sum (Spend) as Spend2 where L4M = 1

I want to thank you for your advance help and do let me know if my question is confusing and I will gladly and immidiately clarify.

[si_the_geek]

SQL varies by which database system (and version) you are using.

If it is SQL Server, you should be able to use this:

SELECT
            SUM (CASE WHEN LTM = 1 THEN SPEND ELSE 0 END) SPEND1,
            SUM (CASE WHEN L4M = 1 THEN SPEND ELSE 0 END) SPEND2
FROM TIME

If you are using Jet (for .mdb files etc) you can use IIF instead of Case.

[Serigraphie]

SQL varies by which database system (and version) you are using.

If it is SQL Server, you should be able to use this:

SELECT
            SUM (CASE WHEN LTM = 1 THEN SPEND ELSE 0 END) SPEND1,
            SUM (CASE WHEN L4M = 1 THEN SPEND ELSE 0 END) SPEND2
FROM TIME

If you are using Jet (for .mdb files etc) you can use IIF instead of Case.

Hello sir,

Thank you very much for your prompt response. I am using Oracle for the DB and I believe this is PL SQL.

Here is the complete query:

SELECT s.BUSINESS_GROUP,
s.DIVISION_ID,
s.DIVISION_NAME,
s.SITE_ID,
s.SITE_NAME,
s.SITE_GEOGRAPHY SITE_REGION,
p.PART_NUMBER,
p.COMMODITY_CODE,
p.COMMODITY_TEAM,
p.COMMODITY_FAMILY,
p.LEAD_TIME,
a.ABC_CATEGORY,
sup.SUPPLIER_ID,
sup.SUPPLIER_NAME,
sup.SUPPLIER_COUNTRY,
DECODE (sup.INTERNAL, 1, 'YES', 0, 'NO', 'NO') INTERNAL_SUPPLIER,
DECODE (sup.PSL_SUPPLIER, 1, 'YES', 0, 'NO', 'NO') PSL,
sup.PSL_STATUS,
sup.PARENT_SUPPLIER_ID PARENT_ID,
sup.PARENT_SUPPLIER_NAME PARENT_NAME,
SUM (f.SPEND) SPEND,
SUM (f.RECEIPT_COUNT) RECEIPT_COUNT
FROM BI_SPEND_FACT f
JOIN BI_SITE s
ON f.SITE_OBJ_ID = s.SITE_OBJ_ID
JOIN BI_SUPPLIER sup
ON f.SUPPLIER_OBJ_ID = sup.LOCAL_SUPPLIER_OBJ_ID
JOIN BI_PART p
ON f.PART_OBJ_ID = p.PART_OBJ_ID
JOIN BI_TIME t
ON f.TIME_OBJ_ID = t.OBJ_ID
JOIN BI_PART_ABC_CATEGORY a
ON f.SITE_OBJ_ID = a.SITE_OBJ_ID
AND f.PART_OBJ_ID = a.PART_OBJ_ID
WHERE t.LTM = 1


Do let me know if you need more info.

[si_the_geek]

I'm afraid I don't use Oracle, so I don't know what method(s) it supports... all I can really suggest is checking whether it supports Case or IIF.

Hopefully somebody with Oracle experience will have ideas.

[Serigraphie]

Not a problem, thank you for your effort sir.

[FunkyDexter]

Haven't touched PL QSL for years but here's a different way that's entirely Ansii SQL:-

Select
  (Select Sum(Spend) From Time Where LTM=1) Spend1,
  (Select Sum(Spend) From Time Where L4M=1) Spend2,
  YourOtherColumns
From YouJoinedDataSet

Basically you can select the result of an inner select as long as the inner select only returns a single value per row. So just drop out the Sum(Spend) Spend1 field from the your select and replace it with full inner selects like this.

In the interests of full disclosure I should probably state that this isn't a good way of doing this and will probably not be the most performant way of doing things but if you're after a quick and dirty solution then it's should do.

I do know that Oracle supports "Analytic functions" which are probably the correct way to go about this buit I won't even try and get the syntax right off the cuff.

[Serigraphie]

@FunkyDexter, let me try that. Thank you!