Displaying the COUNT in a SQL database

Tags:
COUNT
SQL Database
I have the following information available from my SQL database: Incident Type COUNT ADHOCREPAIR 13 CONSEXCHANGE 7094 ENTERPRISE 21189 FONECARE 38996 FONECAREEXCHANGE 6280 FONECAREPRIORITY 1251 POSTALEXCHANGE 929 POSTALREPAIR 4575 RETOUT 1 I also have the specification that ADHOCREPAIR CONSEXCHANGE FONECARE FONECAREEXCHANGE FONECAREPRIORITY POSTALEXCHANGE POSTALREPAIR falls under the CBU group and ENTERPRISE falls under the EBU group. With that in mind, how do I now display the COUNT grouped on CBU and EBU? There is no database column that holds the information.
ASKED: October 21, 2008  7:55 PM
UPDATED: October 22, 2008  3:31 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Without having a column that holds the information, is there another table which holds the parent? If not can you create one? If you can create a new table then join to it. Then use the WITH ROLLUP option to get the rollup values. If you can’t create a new table you can do this with a CASE statement.

<pre>SELECT CASE WHEN type IN (‘ADHOCREPAIR’, ‘CONSEXCHANGE’, ‘FONECARE’, ‘FONECAREEXCHANGE’, ‘FONECAREPRIORITY’, ‘POSTALEXCHANGE’, ‘POSTALREPAIR’) THEN ‘CBU’ ELSE ‘UBU’ END, Type, count(*)
FROM YourTable
GROUP BY CASE WHEN type IN (‘ADHOCREPAIR’, ‘CONSEXCHANGE’, ‘FONECARE’, ‘FONECAREEXCHANGE’, ‘FONECAREPRIORITY’, ‘POSTALEXCHANGE’, ‘POSTALREPAIR’) THEN ‘CBU’ ELSE ‘UBU’ END, Type
WITH ROLLUP</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following