PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Find count of rows having a text on multiple columns. Need an alias row to display

I have a table with multiple columns.’Product Name’ being one. Table has records for multiple products. product name is also used in a free text field. I need reports on how many rows have a particular product listed in ‘Product Name’or in ‘Free text’ fields.
Table as below :

Product Name Issue description
Microsoft outlook not working
Lenovo laptop blue screen
Samsung microsoft word issu

I need the results as below :

Product number of records
Microsoft 2 ( one for outlook issue and other for word issue on a different product)

I need the product ( could be an alias), but need ‘Microsoft’ in the results
Is this achievable ?

I tried sub queries using ‘WITH’ and AS. Doesn’t work.

I’m using a reporting tool with freehand sql. Could not achieve with sub-queries. Appreciate any guidance.

Tags:

2

Answers


  1. 0

    You can achieve this using a UNION to combine results from both the Product Name and the Issue Description columns. Here’s a SQL query that should work for your case:

    SELECT 'Microsoft' AS Product, COUNT(*) AS number_of_records
FROM (
   SELECT "Product Name"
   FROM your_table
   WHERE "Product Name" = 'Microsoft'
   
   UNION ALL
   
   SELECT "Issue description"
   FROM your_table
   WHERE "Issue description" LIKE '%Microsoft%'
) AS combined_results
    Login or Signup to reply.
  2. 0 
    SELECT 
    product_name,
    COUNT(issue) AS issue_count,
    CONCAT(COUNT(issue), ' (', STRING_AGG(issue, ' and '), ')') AS number_of_records
FROM 
    product
GROUP BY 
    product_name;

    By using STRING_AGG function combined with a GROUP BY clause to group the issues by the product name and concatenate them if there are multiple issues for the same product.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search