This topic contains 0 replies, has 1 voice, and was last updated by VGlushkin 10 years, 3 months ago.
Viewing 1 post (of 1 total)
Viewing 1 post (of 1 total)
You must be logged in to reply to this topic.
This topic contains 0 replies, has 1 voice, and was last updated by VGlushkin 10 years, 3 months ago.
Just recently I discovered that it is possible to output COUNT(*) results in several columns, if use a conditional sum technique.
Imagine that you need to output 3 columns: Branch Plant, Number of accounts with bill flag “Y” and number of accounts with bill flag “N”. It means you need to count two different conditions on the same output line. Normal COUNT(*) mechanism doesn’t allow to do that. But it is still possible.
SELECT GMMCU, sum(case when gmbill = 'Y' then 1 else 0 end) AS
COUNT_Y, sum(case when gmbill <>'Y' then 1 else 0 end) AS
COUNT_OTHER FROM F0901 WHERE gmobj = '14523' GROUP BY GMMCU HAVING
sum(case when gmbill = 'Y' then 1 else 0 end)> 0 ORDER BY GMMCU
The 3-column output for this expression would look like this:
=========================================
Business COUNT_Y COUNT_OTHER
Unit
=========================================
522037 1 0
523260 4 0
523262 6 0
523263 4 0
523264 7 3
=========================================
Let’s see that this SQL statement actually does?
Instead of COUNT(*), we are using here SUM() with condition iside:
sum(case when gmbill = 'Y' then 1 else 0 end)
That way we can group by one field, and output all kind of summarized information about records of that type in next columns. Perfect. Enjoy!
You must be logged in to reply to this topic.
Posted | Modified |
---|---|
Nov 16 | Dec 2 - 14:52 |
2017-11 - From the Editor - Who is My President?... | |
Nov 29 | Nov 26 - 14:21 |
Sat *WARREN* 09:00 - - - Sun *BROWN* 09:00... | |
Nov 26 | Nov 26 - 14:19 |
19 Game Results - 2017-11-26 SU - Diego dropped others - Andres scored 4... | |
Nov 24 | Nov 24 - 18:10 |
Adventures in the Dominican... | |
Oct 11 | Nov 17 - 20:32 |
Home... |
Rani S | Nov 15 |
Nice lesson, thank you for sharing with us :)... | |
Scottgal | Nov 4 |
Всем привет, уже третий день не могу поставить D... | |
useaglefcu.org | Jul 4 |
In MongoDB, is it possible to update the value of ... | |
Andre | Jun 24 |
Works with Server 2016 IIS 10.... |
|
Copyright © 2024
Domain Web Center . All Rights Reserved. Magazine Basic theme designed by Themes by bavotasan.com . Disclaimer | Powered by WordPress and bbPress. | Contact the Editor |