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, : 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.
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, : 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.
- 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 : 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?.