SQL - Pivot table and group by not working
You can't GROUP BY activity, document, extraction within the PIVOT table operator, the PIVOT operator infers the grouped columns automatically. But you can write it this way:
WITH Pivoted
AS
(
SELECT *
FROM table1
PIVOT
(
sum([Count]) FOR [Type] IN ( Document ,
Extraction)
) AS p
)
SELECT
product,
SUM(Users) AS TotalUsers,
MAX(DAte) AS LatestDate,
MAX(Document) AS Document,
MAX(Extraction) AS Extraction
FROM Pivoted
GROUP BY Product;
SQL Fiddle Demo
This will give you:
| PRODUCT | TOTALUSERS | LATESTDATE | DOCUMENT | EXTRACTION |
-------------------------------------------------------------------------------
| prodA | 60 | March, 02 2013 02:00:00+0000 | 113 | 152 |
| prodB | 45 | March, 02 2013 02:00:00+0000 | 40 | 73 |
Update 1
WITH a
AS(
SELECT
activity,
username,
[Last Accessed] = max(DATEADD(dd,
DATEDIFF(d, 0, ActDateTime),
0)),
--[#Users] = count(distinct username),
CASE
WHEN COUNT(activity) IS NOT NULL THEN 1
ELSE 0
END AS Count,
CASE
WHEN pageURL LIKE '/Document%'
OR pageURL LIKE '/Database%' THEN 'Document'
ELSE 'Extraction'
END AS [Type]
from activitylog
where pageURL not like '%home%'
AND pageURL not like '/Default%'
group by activity,
username,
...
), Pivoted
AS
(
SELECT *
FROM a
PIVOT
(
sum([Count]) FOR [Type] IN ( Document ,
Extraction)
) AS p
)
SELECT
product,
SUM(Users) AS TotalUsers,
MAX(DAte) AS LatestDate,
MAX(Document) AS Document,
MAX(Extraction) AS Extraction
FROM Pivoted
GROUP BY Product;
GROUP BY statement with a PIVOT command in T-SQL
Use conditional aggregation. It is much simpler:
SELECT [Load ID],
MIN(CASE WHEN [Sequence] = 1 THEN [Stop Zip] END) as [1],
MIN(CASE WHEN [Sequence] = 2 THEN [Stop Zip] END) as [2],
MIN(CASE WHEN [Sequence] = 3 THEN [Stop Zip] END) as [3],
MIN(CASE WHEN [Sequence] = 4 THEN [Stop Zip] END) as [4],
FROM TMS_Load_Stops
GROUP BY [Load ID];
The PIVOT doesn't work because you have additional columns in the table being pivoted. I just don't like the syntax or how it works, but you can also fix it by only selecting the columns you need:
SELECT [Load ID], [1], [2], [3], [4]
FROM (SELECT [Load ID], [Sequence], [Stop Zip]
FROM TMS_Load_Stops
) ls
PIVOT (
MIN([Stop Zip])
for [Sequence] IN ([1],[2],[3],[4])
) PivotTable
;
Why is my Pivot query not grouping properly?
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression (Type in your case) into multiple columns in the output (Actual, Budget, and runs aggregations where they're required on any left over column values that are wanted in the final output.
So, you have to refine the inner query so that only columns necessary are selected:
SELECT [Market], [Actual], [Budget]
FROM
(
SELECT [Market], percentage, type
FROM xxx -- You inner query
) AS src
PIVOT
(
MIN([Percentage])
FOR [Type] IN ([Actual], [Budget])
) AS pvt_table
This means you have to leave columns RN, Total out.
Sql Server pivot table not grouping result set
You don't even need the group by in the query. Because what a pivot does is that it "group by" on the other columns. The key to this solution is the inner select. I think it is not a god idé to first do a sum with group by and then apply a sum and a group by again.
SELECT
EMP_CODE,
[CL],
[LWP],
[PL],
[SL]
FROM
(
SELECT
EMP_CODE,
LEAVENAME,
ACT_DAYS
FROM
@tmp_emp
) L
PIVOT
(
SUM(ACT_DAYS)
FOR LEAVENAME IN ([CL],[LWP],[PL],[SL])
)
AS PVT
ORDER BY EMP_CODE
This will get you the same result.
SQL Pivot Table Not Putting Results In One Line
This could be so much simple using CROSS TABS instead of unpivoting and pivoting the data.
SELECT TID,
MAX( CASE WHEN PlanType = 'AM' THEN [ns] END) AS NS_AM,
MAX( CASE WHEN PlanType = 'AM' THEN [as] END) AS AS_AM,
MAX( CASE WHEN PlanType = 'DB' THEN [ns] END) AS NS_DB,
MAX( CASE WHEN PlanType = 'DB' THEN [as] END) AS AS_DB
FROM MOCK_DATA
GROUP BY TID;
If you want to use dynamic code, it's not that hard to convert. It just becomes an issue of copy-paste-edit for each column that you need to handle, and the code will add as many values for plantype as needed.
DECLARE @SQL nvarchar(MAX)
SELECT @SQL = N'SELECT TID' + CHAR(10)
+ ( SELECT DISTINCT REPLACE( REPLACE(
CHAR(9) + ',MAX( CASE WHEN PlanType = <<quotedplantype>> THEN [ns] END) AS NS_<<plantype>>' + CHAR(10)
+ CHAR(9) + ',MAX( CASE WHEN PlanType = <<quotedplantype>> THEN [as] END) AS AS_<<plantype>>' + CHAR(10)
, '<<quotedplantype>>', QUOTENAME(plantype, '''')), '<<plantype>>', plantype)
FROM MOCK_DATA
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)') +
+ N'FROM MOCK_DATA' + NCHAR(10)
+ N'GROUP BY TID;'
EXEC sp_executesql @SQL /*, @params_def, @param1, @param2,..., @paramN*/;
SQL Pivot report - Not Grouping As Desired
Group it so you get to keep the time portion.
If you need to list all the unique values comma separated instead of max then I have a CLR aggregate function you could use if you are using SQL Server.
SELECT max([time]) AS 'time',
Tagname,
max(GTY) AS 'GTY',
max(TMP) AS 'TMP',
max(FLOW) AS 'Rate',
max(MF) AS 'MF',
max(PRS) AS 'PRS'
FROM y
GROUP BY Tagname
Or wrap your pivot in a group by
SELECT max([time]) AS 'time',
Tagname,
max(GTY) AS 'GTY',
max(TMP) AS 'TMP',
max(FLOW) AS 'Rate',
max(MF) AS 'MF',
max(PRS) AS 'PRS'
FROM (SELECT [time], Tagname, GTY, TMP, FLOW as Rate, MF, PRS
FROM y
PIVOT
(
max(lastvalue)
FOR [tag2] IN (GTY, TMP, FLOW, MF, PRS)
) AS P) y
GROUP BY Tagname
Or better yet, use CTE
WITH PIV AS
(SELECT [time], Tagname, GTY, TMP, FLOW as Rate, MF, PRS
FROM y
PIVOT
(
max(lastvalue)
FOR [tag2] IN (GTY, TMP, FLOW, MF, PRS)
) AS P)
SELECT max([time]) AS 'time',
Tagname,
max(GTY) AS 'GTY',
max(TMP) AS 'TMP',
max(FLOW) AS 'Rate',
max(MF) AS 'MF',
max(PRS) AS 'PRS'
FROM PIV
GROUP BY Tagname
SQL PIVOT Query not working
Accordingly to our discussion in comments to the question...
All what you need to do is to remove reqt_id field from both queries.
SQL pivot and group by month
There were several items wrong in your query. Columns need aliases and your max() within the PIVOT was referencing an invalid column name
Select *
From (
Select [Month] = datename(month,day)
, [Year] = year(day)
, UnitId
, production = sum(pv_production)
From MeterReading
Group By datename(month,day)
,year(day)
,UnitId
) Src
Pivot ( max(production) for [Month] in ([January]
,[February]
,[March]
,[April]
,[May]
,[June]
,[July]
,[August]
,[September]
,[October]
,[November]
,[December] ) ) Pvt
SQL Pivot and group by query
Here I am sharing two way of solving your problem:
Schema:
create table mytable (signalName varchar(20), value int, date date)
insert into mytable values('Alarm', 10, '2020-01-01');
insert into mytable values('Warning', 7, '2020-01-01');
insert into mytable values('Check', 4, '2020-01-01');
insert into mytable values('Alarm', 1, '2020-01-02');
insert into mytable values('Warning', 4, '2020-01-02');
insert into mytable values('Check', 3, '2020-01-02');
Query with simple aggregation and group by:
select max(case when signalname='Alarm' then value end)ValueAlarm,
max(case when signalname='Warning' then value end )ValueWarning,
max(case when signalname='Check' then value end )ValueCheck,
date
from mytable
group by date
Output:
| ValueAlarm | ValueWarning | ValueCheck | date |
|---|---|---|---|
| 10 | 7 | 4 | 2020-01-01 |
| 1 | 4 | 3 | 2020-01-02 |
Related Topics
How to Retrieve Same Column Twice with Different Conditions in Same Table
SQL Inner Join on Select Statements
How to Calculate Balances in an Accounting Software Using Postgres Window Function
Characters That Must Be Escaped in T-Sql
Find Duplicate Records Based on Two Columns
Testing Postgresql Functions That Consume and Return Refcursor
T-SQL Get Number of Working Days Between 2 Dates
How to Get the Latest Record for Each Item in Cosmosdb Using SQL
Like and Null in Where Clause in SQL
Getting a Dynamically-Generated Pivot-Table into a Temp Table
Split Comma Separated String Table Row into Separate Rows Using Tsql
Pass Extra Parameter to Postgresql Aggregate Final Function
Identity-Like Column But Based on Group by Criteria
Is There a Tool for Refactoring SQL, a Bit Like a Resharper for SQL