logo
down
shadow

Total sales amount for customers in cities


Total sales amount for customers in cities

By : user2954508
Date : November 22 2020, 10:31 AM
should help you out Try using a subquery. I know this looks a bit nasty but must be working.
Turns out that you have a duplicate Ordernr (1306) for the same CustomerNr in your Order1 example. I am assuming that this might not be the case in your real data.
code :
SELECT c.CityName, 
(Select SUM(order2.quantity * products.Price) from order1 
INNER JOIN Customers On Customers.CustomerNr=order1.CustomerNr 
INNER JOIN Order2 ON Order2.Ordernr=Order1.Ordernr 
INNER JOIN Products ON Products.ProductNr=Order2.ProductNr 
WHERE Customers.CityName=c.CityName) AS 'TotalSalesAmount'
FROM Order1 o1
INNER JOIN (SELECT o1.CustomerNr 
        FROM Order1 o1 
        GROUP BY o1.CustomerNr 
        HAVING COUNT(o1.CustomerNr) >= 2
       ) AS a ON o1.CustomerNr = a.CustomerNr 
INNER JOIN Order2 o2 ON o1.Ordernr = o2.Ordernr 
INNER JOIN Customers c ON o1.CustomerNr = c.CustomerNr
INNER JOIN Products P ON o2.ProductNr = P.ProductNr
GROUP BY c.CityName;


Share : facebook icon twitter icon
Query to get top 5 users by total month sales amount

Query to get top 5 users by total month sales amount


By : Hassan Riaz Khan
Date : March 29 2020, 07:55 AM
I hope this helps . I was hoping you could help me figure this out. I would like to run a query that would search a table and all it's rows and the date matches the current month. Then return the user_id. What I am trying to achieve is gathering the top 5 user id's under this query. , Are you looking for a query like this?
code :
SELECT user_id
  FROM
(
  SELECT user_id, SUM(amount) total 
    FROM sales 
   WHERE status = 'S' 
     AND MONTH(date) = MONTH(CURDATE())
     AND YEAR(date)  =  YEAR(CURDATE())
  GROUP BY user_id
) q
 ORDER BY total DESC
 LIMIT 5
SELECT user_id 
  FROM sales 
 WHERE status = 'S' 
   AND MONTH(date) = MONTH(CURDATE())
   AND YEAR(date)  =  YEAR(CURDATE())
 GROUP BY user_id
 ORDER BY SUM(amount) DESC
 LIMIT 5
$db  = new mysqli('localhost', 'user', 'password', 'dbname');
if ($db->connect_errno) {
    die('Cannot connect'); // TODO: better error handling
}
$sql = "SELECT user_id 
          FROM sales 
         WHERE status = 'S' 
           AND MONTH(date) = MONTH(CURDATE())
           AND YEAR(date)  =  YEAR(CURDATE())
         GROUP BY user_id
         ORDER BY SUM(amount) DESC
         LIMIT 5";

$topsales = array();

if ($query = $db->prepare($sql)) {
    $query->execute();
    $query->bind_result($user_id);

    while ($query->fetch()) {
        $topsales[] = $user_id;
    }
    $query->close();
} else {
    die('Unable to prepare: ' . $db->error); // TODO: better error handling
}
$db->close();

foreach ($topsales as $topsale) {
    echo $topsale . '<br>';
}
How to get total amount of sales out of the 5 top-selling products?

How to get total amount of sales out of the 5 top-selling products?


By : Peter Gills
Date : March 29 2020, 07:55 AM
should help you out Table structure: , Try a sub-select.
code :
SELECT Order_Details.SKU,
SUM(Order_Details.Quantity * Order_Details.Price) AS Total_Revenue
FROM Order_Details
WHERE Order_Details.SKU IN
 (SELECT X.SKU FROM Order_Details AS X
  ORDER BY SUM(X.Quantity) DESC
  GROUP BY X.SKU LIMIT 5)
GROUP BY Order_Details.SKU;
PL/SQL - Get the most recent month and total sales amount

PL/SQL - Get the most recent month and total sales amount


By : Shane L
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Suppose you have this table structure: , if you just want the most recent month use simple max
code :
-- this should return 4 and 11000
select max(month),sum(total_sales) from table1;
Based on territory, get the top customers and their total sales in percentage

Based on territory, get the top customers and their total sales in percentage


By : user1542397
Date : March 29 2020, 07:55 AM
wish helps you Your query have some issues. You need to calculated the total per territory - not the whole total.
Note, the code below can be separated to individual statements. Also, there are other solutions of this task.
code :
WITH DataSource AS
(
    SELECT DISTINCT TerritoryID
                    ,CustomerID
                    ,SUM(TotalDue) OVER (PARTITION BY TerritoryID,CustomerID) * 100 / SUM(TotalDue) OVER (PARTITION BY TerritoryID) AS [%_of_TotalSale]
    FROM Sales.SalesOrderHeader
), DataSourceUsersRanked AS
(
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY TerritoryID ORDER BY [%_of_TotalSale] DESC) AS RN
    FROM DataSource
), DataSourceUsersFiletred AS
(
    SELECT *
    FROM DataSourceUsersRanked
    WHERE RN <= 5
)
SELECT DSF.TerritoryID
      ,st.[Name]
      ,SUM([%_of_TotalSale]) AS [%_of_TotalSale]
      ,MAX(UserIDs) AS [Top5Customers]
FROM DataSourceUsersFiletred DSF
INNER JOIN Sales.SalesTerritory st
    ON DSF.TerritoryID = st.TerritoryID
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT ',' + CAST(CustomerID AS VARCHAR(12))
            FROM DataSourceUsersFiletred DS1
            WHERE DS1.[TerritoryID] = DSF.[TerritoryID]
            ORDER BY CustomerID
            FOR XML PATH(''), TYPE          
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) Users(UserIDs)
GROUP BY DSF.TerritoryID
        ,st.[Name]
ORDER BY TerritoryID;
trying to find customers and their total sales who have shopped for brand1 as well as brand2

trying to find customers and their total sales who have shopped for brand1 as well as brand2


By : lrq
Date : March 29 2020, 07:55 AM
I hope this helps you . If I understand correctly, you can just use aggregation with an appropriate having clause:
code :
select individual_id, sum(dollar_value_us)
from transaction_detail_mv
group by individual_id
having sum( case when brand_org_code = 'BRAND1' then 1 else 0 end) > 0 and
       sum( case when brand_org_code = 'BRAND2' then 1 else 0 end) > 0 
Related Posts Related Posts :
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • Finding max(date) from the resultant query output
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • Is it possible to maximum occurences of a value for each key in my table all in one query?
  • Divide table into groups of 5000 based on rank
  • rolling back exec and sp_OA methods
  • How to convert multiple rows to one row in SQL Server?
  • How to use substring and charindex in google query language
  • Find table information where part of column matches char variable
  • Need to retrieve rows from table where the following condition was not satisfied column A = column B and column B = colu
  • Assigning an index to a constraint
  • Retrieve records from multiple Records returned by Sub-Query
  • SQL DELETE group of records based on opposite group being empty
  • SQL GetDate() returns wrong time
  • SQL Server -- updating the `sys.*` tables and not just reading them
  • SQL Order By using concat
  • SQL server 2008 - subquery returned more than one value
  • Group Data Hourly and Insert Into a Summary Table in Postgres
  • Execute SQL Server Pass-Through Query From Access VBA
  • MS Access SQL Transform Aggregate Manipluation of Values for Pivot
  • SQL "if exists..." dynamic query
  • HANA: Split string?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org