I need to execute query like select myUsualField, SOME_FUNCTION(myAnotherField) as myUnusualField from MYTABLE group by myUsualField, myUnusualField In Hive this query fails: it cannot find field …
Tag: group-by
Using Over Partition in sql compared with group by
Given the table creation code bellow, is there an alternative method(s) to display the same result to using group by and count(*)? And what’s the difference in this case? Answer This query puts the total for each colour on each row: Before window functions, a typical solution would be a correlated subqu…
SQL – Count rows based on matching columns and value range
Please see below query using T-SQL with SSMS. There are three tables: B, G and L. B has a column Bname G has 2 columns Gname, Gross L has 2 columns Bname, Gname Gross column is an INT ranging …
BigQuery – How to find count of Unique overlapping values in 1 or or more categories (Count of categorical values)?
I am very new to BigQuery and standard SQL. I might not be able to figure out the correct approach to solve a problem. Please help me out. Please help me change this code to get the desired output. I have a Color Column and a ID column. Example shown below: Color | ID Blue | id_1 Blue | id_5
How to get first row of data for each month (postgres)
I am a beginner in postgres and would like to get the first row for each month (group by) but I’m not really sure how. My table order is as per below: order_id | cust_id | order_date ——————…
Mysql Conditional Aggregation Using group_by query and Case to group Month and Year
I’ve a table structure where I’m saving the data in the below format. The ledger amount needs to be divided into two columns and grouped through the sum equation. Those rows having receipt_id not as null their ledger_amount needs to be displayed as sum(ledger_amount) as credit whole rows having pa…
Group and subquery issue
This is my sample data CREATE TABLE customer1 ( rating int(9), genre varchar(100), title varchar(100) ); INSERT INTO customer1 (rating, genre, title) VALUES (2, ‘A’, ‘abc’), (4, ‘A’, ‘…
How to group by month and year?
I have a table PURCHASE with a date column PURCHASEDATE which is in DATE format. I’m trying to get the purchases grouped by month and year. To do so, I try with: I have also tryied with GROUP BY EXTRACT(MONTH FROM PURCHASEDATE), EXTRACT(YEAR FROM PURCHASEDATE) but neither worked. I’m rusty with SQ…
Update column values with result from group values (MIN) of linked table
I have two tables Table 1 (main) and Table 2,connected with T1. I need to delete double entries in Table 1. TO do that before any delete in T1 I must delete or update the data in T2. The option chosen is to update T2 data to point with entries not to be deleted in T1. SQL Problem : I
Calculate Median for each group in AWS Athena table
Below is the schema for the athena table I wish to calculate median for ‘parameter_value’ group by standard_lab_parameter_name & units. For this I followed link : https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html But on running the query It throws error Any help? Or if some alternat…