Скачать или смотреть Mastering SQL Pivot: Count and Sum Across Multiple Columns

Learn how to effectively use SQL Pivot to count and sum values from multiple columns in a single query. Get clear examples and explanations for better understanding.
---
This video is based on the question https://stackoverflow.com/q/64177421/ asked by the user 'q phan' ( https://stackoverflow.com/u/13253745/ ) and on the answer https://stackoverflow.com/a/64177553/ provided by the user 'John Cappelletti' ( https://stackoverflow.com/u/1570000/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL pivot count and sum 2 columns

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering SQL Pivot: Count and Sum Across Multiple Columns

In the world of data management, SQL (Structured Query Language) plays a crucial role. One common task that developers encounter is the need to summarize data from tables using pivots. A user recently faced a challenge when trying to count and sum values across two different columns from a table. In this guide, we will break down the problem and offer efficient solutions that can help you masters SQL pivots.

The Problem

The user started with a table named table3, which contained the following data:

shopidtimestimes2shop151shop222shop161shop111shop212The objective was to create a pivot table that not only counts the times for each shopid but also summarizes the times2 values. The initial attempts to create the pivot table successfully counted values for the times column but fell short when trying to include times2.

The original SQL statement used was:

[[See Video to Reveal this Text or Code Snippet]]

Output of the Original Query

shop1Shop232Expected Output

The expected result should account for both counts and sums:

shop1Shop264The Solution

To achieve the desired output, the user needs to either perform a union of the two column values or utilize a SUM() function instead of COUNT() in their pivot query. Let's explore both methods below.

Method 1: Using UNION ALL

One effective solution is to use the UNION ALL command. This can combine results from both columns before performing the pivot. Here's how this works:

[[See Video to Reveal this Text or Code Snippet]]

Results with UNION ALL

shop1Shop264Using this method counts all instances from both the times and times2 columns effectively, yielding the combined results you need.

Method 2: Using SUM Function

Another approach is to adjust the existing pivot query by changing the COUNT() function to SUM(). This method allows you to get the total for the desired columns without needing a union. Here's an example:

[[See Video to Reveal this Text or Code Snippet]]

Results with SUM Function

shop1Shop264Conclusion

Using SQL pivots can be a powerful way to rearrange and summarize data. In this case, we explored two effective methods to combine counts and sums of two different columns. Whether you choose to leverage the UNION ALL or the SUM() function, both can yield the results you expect. Armed with these techniques, you can confidently tackle SQL pivot challenges in your future projects.

Feel free to experiment with these queries in your SQL Server, and transform how you manage your data today!