Hot answers tagged

4

Something like: select hour_of_day, avg( case when day_of_week = 2 then item_count else null end ) Mondays, avg( case when day_of_week = 3 then item_count else null end ) Tuesdays, avg( case when day_of_week = 4 then item_count else null end ) Wednesdays, avg( case when day_of_week = 5 then item_count else null end ) Thursdays, ...


3

welcome to dba.stackexchange. It might help to paste your (unsuccesful) queries and the errors you got. I am sorry other tipps and tutorials did not help you with your challenge. Since you are specifically asking for pivot, I suggest another documentation that appears (to me) to be quite straight forward: https://docs.microsoft.com/en-us/sql/t-sql/queries/...


3

This answer isn't dynamic, but shows you how to achieve the goal in pure TSQL, making it dynamic should be straightforward. Essentially, you just need to add another column to spread the data out so that the MAX(fieldname) isn't the max for the event. I've chosen to use a ROW_NUMBER partitioned by the EventID so that each entry for each event is also ...


2

You can use filtered aggregation for this: select v.poll_id, count(*) filter (where v.option = p.option_a) as option_a, count(*) filter (where v.option = p.option_b) as option_b, max(p.created_at) as created_at from votes v join polls p on p.id = v.poll_id group by v.poll_id order by v.poll_id; The max(p.created_at) is necessary to ...


2

Unfortunately, MySQL doesn't have an UNPIVOT operator which would be very handy in this case - you'll just have to roll up your sleeves and do it manually I'm afraid. See the fiddle here - I've only done 3 CFWs - you can just copy and paste for the remainder. CREATE TABLE example ( opp_id INTEGER NOT NULL PRIMARY KEY, region VARCHAR (10) NOT NULL, ...


2

You can use a SUM(CASE... and a GROUP BY SELECT TableLead.StaffID, Staff.StaffName, SUM(CASE WHEN TableLead.LeadStatus = 'New' THEN 1 ELSE 0 END) NewLeadAccount, SUM(CASE WHEN TableLead.LeadStatus = 'Qualified' THEN 1 ELSE 0 END) QualifiedLeadAccount FROM TableLead JOIN Staff ON Staff.StaffID = TableLead.StaffID GROUP BY ...


2

Why do you not simply unpivot the hole table SELECT * FROM push_data_temp; ID_PK | ID | COL1 | COL2 ...


2

You're only getting one row because there is no other column/value to have PIVOT produce different groups. Try: SELECT Doctor ,Actor ,Singer ,Professor FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS Dummy ,Name ,Occupation FROM Occupations ) o PIVOT ( MAX(Name) FOR Occupation IN (Doctor, ...


2

What you are missing is a row that can be grouped by. Row_NUMBER is a good solution CREATE TABLE OCCUPATIONS (Name varchar(255), Occupation varchar(255)) ; INSERT INTO OCCUPATIONS (Name, Occupation) VALUES ('Samantha', 'Doctor'), ('Julia', 'Actor'), ('Maria', 'Actor'), ('Meera', 'Singer'), ('Ashely', 'Professor'), ('Ketty', '...


1

every further filter you want to add simply increase the number in the IN Clauses If you have a new attrubute you have to add another AND in the WHERE clause As i said in the comment attribute_id in attribute_values is redundant CREATE TABLE fabric_attribute_value (`id` varchar(3), `attribute_value_id` varchar(19), `fabric` varchar(6), `attribute_id` ...


1

WITH Sales_CTE ([50001 Sales], [50002 Sales], [50001 margin],[50002 margin],[50001 profit],[50002 profit],[50001 cost],[50002 cost]) AS ( select * from ( select rtrim(cast(csstor as char))+' Sales' as s ,rtrim(cast(csstor as char))+' margin' as m ,rtrim(cast(csstor as char))+' profit' as p ,rtrim(cast(...


1

This article was super helpful: https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html and the demo of particular use: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=407a37686238bb3fbcbc4285d1705871 Unfortunately it turns out using a Pivot/CrossTab is much more complex than dynamically generating the original query with code: SELECT employeeid, ...


1

In Mysql You can use SUM with an comparision, which returns 1 if the condition uis met or 0 if not. SELECT `BSEG_ID`, `DST_ID`, SUM(`BILL_SQ` BETWEEN 0 AND 10) '0-10', SUM(`BILL_SQ` > 10 AND `BILL_SQ` <= 20) '10> <= 20', SUM(`BILL_SQ` > 20) '>20' FROM CI_BSEG_CALC_LN GROUP BY `BSEG_ID`, `DST_ID` BSEG_ID | DST_ID | 0-10 | ...


1

SELECT color, AVG(CASE WHEN dimension = 'length' THEN value END) avg_length, AVG(CASE WHEN dimension = 'width' THEN value END) avg_width FROM boxes GROUP BY color; fiddle


1

One query for all polls Use the aggregate FILTER clause, like a_horse already suggested. See: Return counts for multiple ranges in a single SELECT statement SELECT p.id AS poll_id -- ① PK column! , min(ct) FILTER (WHERE v.option = p.option_a) AS option_a , min(ct) FILTER (WHERE v.option = p.option_b) AS option_b , p.created_at -- ① no ...


1

You're looking for the PIVOT clause. You can see a working example in this db<>fiddle. The query is: SELECT customer_num ,p.I AS I_Branch_Code ,p.Y AS Y_Branch_Code ,p.L AS L_Branch_Code FROM ( SELECT customer_num ,Channel_Id ,Branch_code FROM [table] )s PIVOT ( MAX(Branch_Code) FOR Channel_Id IN ([I], [Y], [L]) ) p


1

So the pieces are there, but not structured in a way for a database to quickly access the data you need, or to allow growth without major restructuring. In your case the best way to do that is to arrange the data in a longer format instead of the wide format you have. So instead of having each column represent an entity identifiable only through a metadata ...


1

As i said in the comment simply Replace the SUM with what ever you need in your Create Table `data` ( callDate text ,callerName text ,callLength text ); Insert Into `data` (`callDate`, `callerName`, `callLength`) Values ('07/01/2020', 'Client A', '0:00:23'), ('07/01/2020', 'Client B', '0:15:23'), ('07/01/2020', 'Client C', '0:7:10'), ('07/02/2020', ...


1

The query below returns exactly what you described in your question but I doubt this is what you really need. SET @sql = NULL; SELECT GROUP_CONCAT( CONCAT('(SELECT numbers FROM phones WHERE owner_id = ',id, ' ORDER BY numbers LIMIT 1 OFFSET ',rn - 1,' ) AS numbers', rn) ) INTO @sql FROM (SELECT o.id, p.numbers, @rn := @rn + 1 AS rn FROM owner o INNER JOIN ...


1

Use conditional aggregation: select count(*) filter (where category = 'category1') as category1, count(*) filter (where category = 'category2') as category2, count(*) filter (where category = 'category3') as category3 from the_table;


Only top voted, non community-wiki answers of a minimum length are eligible