logo
Tags down

shadow

Trying to get many SQL results into a single table


By : Reda M. Hussien
Date : September 14 2020, 10:00 PM
help you fix your problem Seems like you want to learn about GROUP BY? Your intention seems to be to get the sum of the orders' values per month in the given time period. I don't think you need any procedural code for that at all. Try:
code :
SELECT concat(year(orderdate),
              month(orderdate),
              '01') month,
       sum(ordertotal) ordertotal
       FROM dbo.tb_order
       WHERE applicationid = '24b48d78-1fb8-4842-af6d-11c58e940700'
             AND orderstatusid IN (10,
                                   20,
                                   30)
             AND orderdate >= '20160101'
             AND orderdate < '20200301'
       GROUP BY year(orderdate),
                month(orderdate)
       ORDER BY year(orderdate),
                month(orderdate);


Share : facebook icon twitter icon

concatenate multiple results of table-valued function into a single table


By : Alaa
Date : March 29 2020, 07:55 AM
With these it helps You don't need a table valued function, use XPath to extract these values directly in a SELECT statement
code :
SELECT 
    Data.query('data(/xpath/to[@your="type"])') AS type,
    Data.query('data(/xpath/to[@your="value"])') AS value
FROM Test
/* JOINs, WHERE HAVING, GROUP BY and/or ORDER BY clauses */
DECLARE @testTable TABLE(
    XmlData XML
)

INSERT INTO @testTable (XmlData)
VALUES ('<row><node><key>key11</key><value>value11</value></node><node><key>key12</key><value>value12</value></node></row>')

INSERT INTO @testTable (XmlData)
VALUES ('<row><node><key>key21</key><value>value21</value></node><node><key>key22</key><value>value22</value></node></row>')

INSERT INTO @testTable (XmlData)
VALUES ('<row><node><key>key31</key><value>value31</value></node><node><key>key32</key><value>value32</value></node></row>')

SELECT 
    nref.value('key[1]', 'nvarchar(50)') AS [key],
    nref.value('value[1]', 'nvarchar(50)') AS value
FROM @testTable CROSS APPLY XmlData.nodes('//node') AS R(nref)
key11   value11
key12   value12
key21   value21
key22   value22
key31   value31
key32   value32

Returning results from within a single table ordered by how many results


By : vineeth valambra
Date : March 29 2020, 07:55 AM
Hope that helps UPDATE:
Based on the update to your question (including the desired resultset), here's a query that returns that resultset. (This is VERY similar to the query in the inline view explained in my original answer)
code :
  SELECT i.user_id                         AS user_id
       , MAX(IF(i.item_id= 2   ,1,0))      AS primary_item
       , MAX(IF(i.item_id= 122 ,1,0))      AS secondary_item
       , MAX(IF(i.item_id= 132 ,1,0)) +
         MAX(IF(i.item_id= 126 ,1,0))      AS other_items
    FROM user_items i
   WHERE i.item_id IN (2, 122, 132, 126)
   GROUP BY i.user_id
  HAVING primary_item
   ORDER 
      BY primary_item   DESC
       , secondary_item DESC
       , other_items    DESC
       , i.user_id
       , MAX(IF(i.item_id= 132 ,1,0)) +
         MAX(IF(i.item_id= 133 ,1,0)) +
         MAX(IF(i.item_id= 135 ,1,0)) +
         MAX(IF(i.item_id= 137 ,1,0)) +
         MAX(IF(i.item_id= 143 ,1,0))      AS other_items
       , MAX(i.item_id= 2)                 AS primary_item
       , MAX(i.item_id= 122)               AS secondary_item
SELECT d.user_id
     , d.item_id 
  FROM user_items d
  JOIN ( 
         SELECT i.user_id
              , MAX(IF(i.item_id=2  ,1,0)) AS item_2
              , MAX(IF(i.item_id=122,1,0)) AS item_122
              , MAX(IF(i.item_id=132,1,0)) AS item_132
           FROM user_items i
          WHERE i.item_id IN (2, 122, 132)
          GROUP BY i.user_id
         HAVING item_2
          ORDER BY 3 DESC, 4 DESC, 1
       ) f
    ON d.user_id = f.user_id
 WHERE d.item_id IN (2, 122, 132)
 ORDER BY (f.item_122 AND f.item_132) DESC
        , f.item_122 DESC
        , d.user_id
        , d.item_id
         SELECT i.user_id
              , MAX(IF(i.item_id=2  ,1,0)) AS item_2
              , MAX(IF(i.item_id=122,1,0)) AS item_122
              , MAX(IF(i.item_id=132,1,0)) AS item_132
           FROM user_items i
          WHERE i.item_id IN (2, 122, 132)
          GROUP BY i.user_id
         HAVING item_2
          ORDER BY 3 DESC, 4 DESC, 1
         HAVING item_2 > 0 

How to get two results as single table


By : Banh mi
Date : March 29 2020, 07:55 AM
I wish did fix the issue. If you have a relation to use then you can just join the tables as normal:
code :
WITH Eduction AS
(   SELECT  item,
            ID = ROW_NUMBER() OVER(ORDER BY Item)
    FROM    fnSplit((SELECT DegreeType FROM HR_EmpEducation WHERE EmpID = 9), '|')
), Schools AS
(   SELECT  item,
            ID = ROW_NUMBER() OVER(ORDER BY Item)
    FROM    fnSplit((SELECT School_Inst FROM HR_EmpEducation WHERE EmpID = 9), '|')
)   
SELECT  Eduration = ISNULL(e.Item, ''),
        Schools = ISNULL(s.Item, '')
FROM    Education e
        FULL JOIN Schools s
            ON e.ID = s.ID;

How can I get the latest two dates from a single column, in a single table, for two separate results of a WHERE clause?


By : Norakmal Hakim
Date : March 29 2020, 07:55 AM
I hope this helps . You can put a IF inside the max function to get the max of only certain values.
code :
DATE_FORMAT(FROM_UNIXTIME(MAX(IF(n.data LIKE '%buyer-contact%',n.datecreated,NULL))), '%d-%m-%Y') AS `Last Buyer Contact Note`
DATE_FORMAT(FROM_UNIXTIME(MAX(IF(n.data LIKE '%seller-contact%',n.datecreated,NULL))), '%d-%m-%Y') AS `Last Seller Contact Note`

Multiple MySQL Table JOINS needs to concat the results of 1 table into a single column field


By : Joseph E
Date : March 29 2020, 07:55 AM
may help you . Try something like this: group_concat(comments order by id asc separator ' ') as comment
Note that order by id should be the id of the comment (so the comments are correctly ordered when concatenated). :
code :
SELECT sfo.order_id, sfo.order_number, sfo.item_status, shipping.city, comments.comment
FROM nam_order_items AS sfo
JOIN sales_flat_order_address AS shipping ON shipping.parent_id = sfo.order_id
AND shipping.address_type =  'shipping'
JOIN
(
  select
    parent_id,
    group_concat(concat(created_at, ' - ', comment) order by entity_id asc separator ' ') as comment
  from
    sales_flat_order_status_history
  group by
    parent_id
) comments ON 
  comments.parent_id = sfo.order_id
Related Posts Related Posts :
  • Table ID (PrimaryKey) does not increment in a proper sequence in SQL Server
  • Is there an SQL operator in Amazon Redshift which is working as LIKE and IN operator together?
  • Group by two columns expecting all the values, selected from other table, in second column in oracle
  • Find average number of orders per customer in last three years?
  • REGEXP_SUBSTR Oracle | Extract a string between two delimiters
  • SQL view, default value if null
  • Time offset (Paris-Kuwait) incorrect timezone difference SQL Server
  • How to check period id and last updated value then assign to variable?
  • Update different columns in multiple tables by joining them
  • SQL query that prevents Excel from converting long integer to scientific notation
  • How to make valid combinations of many separate tables with subcombinations
  • SELECTING from same field in 2 column
  • ORA-38104: Columns referenced in the ON Clause cannot be updated c.emp_id
  • TSQL: Case when ... end as '
  • SQL Server trigger that stops rows from being added to a table if one of the columns has a value that is not already in
  • What's the recommended way to do database migrations with Ktor + Exposed (Kotlin)?
  • PL/SQL Autoregressive forecasting
  • Extract data from XML using T-SQL query
  • Splitting a column and adding text value to the numerical value
  • selecting minimum value depending on other value
  • SQL update id in table except rows with duplicate data in column
  • SQL Execute a procedure only when the table is not empty?
  • how to derive filter condition from table rows
  • Adding same ID to same rows
  • ODBC Connection / Crystal Reports
  • Problems with updating column from another table in postgresql
  • Querying a messy table in SQL
  • Marking users as not active if they don't have required records
  • Check constraint depending on another table (mutually exclusive relation)
  • Order by error in create view in SQL Server
  • SQL Double Group by query
  • How to use alias in INSERT query?
  • Exempting sunday from oracle sql script
  • Using SQL CONTAINS with multiple words inside a variable
  • Subqueries vs Multi Table Join
  • How to get an array subquery using postgress sql?
  • SQL: How to group records, when they have different types
  • Passing NULL as Value in Parameter for T-SQL stored procedure
  • sql queries side by side
  • Does ROW_NUMBER() support OVER(ORDER BY 1)?
  • PostgreSQL calculate difference between multi rows
  • multiple where statements in proc sql
  • Find the Week from a date passed as input oracle
  • Get month wise YTD
  • can we use two different queries with CTE table in sql server
  • what is actual difference between or operator and in operator in sql
  • How to improve the execution time of inserting to a table from selecting rows from a table of million rows in ORACLE?
  • How do I Select Counts with Spark SQL Without Getting Errors?
  • SQL Query to Fetch Two Records Per Each ID
  • what is better (if) or (where) in sql?
  • SQL query for output
  • Oracle SQL Developer: stop script running if error
  • SQL distinct column with sum of corresponding value
  • Why any data is shown when I execute this select in sql?
  • mariadb sql select distinct values from json array
  • Is there a way to split sql results by year?
  • Unable to grant select privilege to role I've created
  • Unable to grant role to user
  • ORA-30009: Not enough memory for CONNECT BY operation / ORA-01788: CONNECT BY clause required in this query block
  • Case when then in where clause
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk