How to get multiple results based on two different conditions from one table using single query

I have a table which list number of sales for each day and the related sales amount. Following is the table SalesMaster.

Date     Item Sales Amount
30062018 1    5     50000
30062018 2    5     50000
30062018 3    5     50000
30062018 4    5     50000
30062018 5    5     50000
30062018 6    5     50000
30062018 7    5     50000
31052018 1    5     50000
31052018 2    5     50000
31052018 3    5     50000
31052018 4    5     50000
31052018 5    5     50000
31052018 6    5     50000

From this table I want to get the following result. I want to list the number of Sales for each item for each month end which is like below.

Item May_Sales June_Sales
1    5000      5000
2    5000      5000
3    5000      5000
4    5000      5000
5    5000      5000
6    5000      5000
7    5000      5000

Following is the code I wrote but it says, [1]: ORA-01427: single-row subquery returns more than one row.

SELECT t1.item,
       sales AS may_sales,
       (SELECT sales
          FROM salesmaster t1
         WHERE t1.date = '30-June-2018')
           AS june_sales
  FROM salesmaster t1
 WHERE t1.date = '31-May-2018'

For the results you want to achieve, conditional aggregation will be the best approach:

select 
  item,  
  sum(case when to_date(Date, 'DDMMYYYY') between to_date('01052018','YYYYMMDD') and to_date('31052018','DDMMYYYY') then Amount else 0 end) may_sales, 
  sum(case when to_date(Date, 'DDMMYYYY') between to_date('01062018','YYYYMMDD') and to_date('31062018','DDMMYYYY') then Amount else 0 end) june_sales
from SalesMaster
group by item
order by item;

You have to convert your Date field into valid fields to get the monthly aggregates.

Selecting two values from the same table but under different conditions, For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters. A simple SELECT statement is the most basic way to query multiple tables. You can call more than one table in the FROM clause to combine results from multiple tables. Here’s an example of how this

select item, may.sales as MAY_sales, JUNE.sales as JUNE_sales  from
(SELECT distinct item
FROM salesmaster )MAST  
left join 
(SELECT item , sales
FROM salesmaster 
WHERE date = '31052018') MAY
on (MAST.item = JUNE.item)
left join 
(SELECT item , sales
FROM salesmaster 
WHERE date = '31052018') JUNE
on (MAST.item = JUNE.item)

How to Calculate Multiple Aggregate Functions in a Single Query , How to Calculate Multiple Aggregate Functions in a Single Query This solution allows for calculating all results in a single query by using 8 different, explicit, 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. SELECT. count (*), The PIVOT clause can be applied to a table expression to “pivot” it in a  Double-click each of the tables you want to show, and then click Close. If you do not find a relationship between the two tables, create one by dragging a field from one of the tables to a field on the other table. The fields on which you create the relationship between the tables must have identical data types.

I would just use conditional aggregation:

SELECT sm.item,
       SUM(CASE WHEN t1.date = '31052018' then sales END) as may_sales,
       SUM(CASE WHEN t1.date = '30062018' then sales END) as june_sales
FROM salesmaster sm
GROUP BY sm.item
ORDER BY sm.item;

Your first column doesn't look like a date, so don't use date comparisons on it. If it is a date, then use:

SELECT sm.item,
       SUM(CASE WHEN t1.date = DATE '2018-05-31' THEN sales END) as may_sales,
       SUM(CASE WHEN t1.date = DATE '2018-06-30' THEN sales END) as june_sales
FROM salesmaster sm
GROUP BY sm.item
ORDER BY sm.item;

As for your query, it is missing a correlation clause on item in the subquery.

EDIT:

If you want the difference, you need to repeat the expressions. Here is one method:

SELECT sm.item,
       SUM(CASE WHEN t1.date = '31052018' THEN sales END) as may_sales,
       SUM(CASE WHEN t1.date = '30062018' THEN sales END) as june_sales,
       SUM(CASE WHEN t1.date = '31052018' THEN -sales) 
                WHEN t1.date = '30062018' THEN sales
           END) as diff,

Specify Multiple Search Conditions for One Column, Search for several different names in an employee table or for employees to search conditions in both the WHERE and HAVING clauses of a query. of the search and can return more rows than searching for a single value. you to specify that values in a column must meet two (or more) conditions for  Look at the below example where we stated some results based on some conditions. Here the formula which is written below the table is used for cell C2. This formulated cell was dragged down for getting the result for the whole column. The above example follows the nested if functions. The nested if functions in excel follow some rules.

Match two criteria and return multiple records, The image above shows records filtered on items based on condition in B3 and dates based on condition in C3. If both conditions match on the same row the  Using two criteria to return a value from a table. Let’s look at a scenario where you want to use two criteria to return a value. Here’s the data you have: The criteria are “Name” and “Product,” and you want them to return a “Qty” value in cell C18.

SQL query return data from multiple tables, Joining two or more tables using an inner join (See the wikipedia entry for additional info); How to use a It is easier to read queries written by other folks using this syntax. 2 rows in set (0.01 sec) mysql> insert into brands (brand) values ('Ford'), ('Toyota'), We could add another condition to the where clause like this: Following is the code I wrote but it says, [1]: ORA-01427: single-row subquery returns more than one row. SELECT t1.item, sales AS may_sales, (SELECT sales FROM salesmaster t1 WHERE t1.date = '30-June-2018') AS june_sales FROM salesmaster t1 WHERE t1.date = '31-May-2018'

SQL: Combining the AND and OR Conditions, to use the AND condition and the OR condition together in a single query with condition and OR condition can be combined to test for multiple conditions in a If you want to follow along with this tutorial, get the DDL to create the tables and This example would update all favorite_website values in the customers table  From this table I am trying to combine two result sets like: select uom_name as uomname1 from unit_of_measure where uom_id=1; select uom_name as uomname2 from unit_of_measure where uom_id=2; Can anyone help me to join these two result sets. I have tried with union but it didn't solve the issue.

Comments
  • Hi, if I want to get the difference of the two months as another column, I tried the following, SUM (may_sales - june_sales) AS vriance. It doesn't give me a value and says [1]: ORA-00904: "JUNE_SALES": invalid identifier
  • Hi, I tried editing the calculation part only to get the positive values and constants value 0 for the negative ones. I repeated the above conditions but I can only get the 0 for negative ones and I do not get anything for the rest. Any suggestion on this?.