Sum two results from SQL query-CodePudding

I just started learning SQL. I filtered the data I have and accordingly two results came out. I want to collect these results, but whatever I do, it throws an error.

Here is my code:

SELECT TOP (1000) [Sec_id]
      ,[Prc_date]
      ,[Mkt_Price]
      ,[Currency]
      ,[Pricing_factor]
  FROM [EXAMPLE1].[dbo].[market]
  WHERE Currency='INR' OR Currency='AUD';
  
  SELECT  SUM(Mkt_Price)* Pricing_factor as INR FROM market 
  where Currency='INR' and Mkt_Price>100 group by Pricing_factor  ; 

 
 SELECT SUM(Mkt_Price)* Pricing_factor as AUD  FROM market 
  where Currency='AUD' and Mkt_Price>100 group by Pricing_factor  ;

 
  
  SELECT  SUM(Mkt_Price)* Pricing_factor as totalll FROM market 
  where Currency='AUD' or Currency='INR' and Mkt_Price>100 group by Pricing_factor

and I got these results:

How can I collect(SUM) and reflect the resulting data ("INR"," AUD")? (Using SMSS)

CodePudding user response：

SELECT Currency, SUM(Mkt_Price * Pricing_factor) AS Total
FROM market
WHERE Currency IN ('AUD', 'INR') AND Mkt_Price > 100
GROUP BY Currency;

Be careful with operator precedence:

 Currency = 'AUD' OR  Currency = 'INR' AND Mkt_Price  > 100
 Currency = 'AUD' OR (Currency = 'INR' AND Mkt_Price) > 100 -- same thing

This is what you really wanted:

(Currency = 'AUD' OR Currency = 'INR') AND Mkt_Price  > 100

To combine the totals with a grand total you could try a union:

SELECT Currency, SUM(Mkt_Price * Pricing_factor) AS Total
FROM market
WHERE Currency IN ('AUD', 'INR') AND Mkt_Price > 100
GROUP BY Currency
UNION ALL
SELECT 'All', SUM(Mkt_Price * Pricing_factor) AS Total
FROM market
WHERE Currency IN ('AUD', 'INR') AND Mkt_Price > 100;

One you progress past understanding the basic operations you'll want to check into "grouping sets" and "rollup".