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,
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:
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 ...
You can use filtered aggregation for this:
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 ...
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,
You can use a SUM(CASE... and a GROUP BY
SUM(CASE WHEN TableLead.LeadStatus = 'New' THEN 1 ELSE 0 END) NewLeadAccount,
SUM(CASE WHEN TableLead.LeadStatus = 'Qualified' THEN 1 ELSE 0 END) QualifiedLeadAccount
ON Staff.StaffID = TableLead.StaffID
You're only getting one row because there is no other column/value to have PIVOT produce different groups.
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS Dummy
MAX(Name) FOR Occupation IN (Doctor, ...
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
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` ...
WITH Sales_CTE ([50001 Sales], [50002 Sales], [50001 margin],[50002 margin],[50001 profit],[50002 profit],[50001 cost],[50002 cost])
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
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, ...
In Mysql You can use SUM with an comparision, which returns 1 if the condition uis met or 0 if not.
SUM(`BILL_SQ` BETWEEN 0 AND 10) '0-10',
SUM(`BILL_SQ` > 10 AND `BILL_SQ` <= 20) '10> <= 20',
SUM(`BILL_SQ` > 20) '>20'
GROUP BY `BSEG_ID`, `DST_ID`
BSEG_ID | DST_ID | 0-10 | ...
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 ...
You're looking for the PIVOT clause. You can see a working example in this db<>fiddle.
The query is:
,p.I AS I_Branch_Code
,p.Y AS Y_Branch_Code
,p.L AS L_Branch_Code
MAX(Branch_Code) FOR Channel_Id IN ([I], [Y], [L])
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 ...
As i said in the comment simply Replace the SUM with what ever you need in your
Create Table `data`
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'),
The query below returns exactly what you described in your question but I doubt this is what you really need.
SET @sql = NULL;
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 ...