CODEWITHSUNDEEP
mysqlsql
user3377061
user3377061

Reputation: 37

Totalizing and grouping data from an SQL query

I have a query that will display a list of products in order of their import/export property.

select i.`itemid`, p.`prodid`,
(
   (case when p.`desc` like "Import/Export" 
        then 100 else 0 end) +
   (case when p.`desc` like "Export"  
        then 70 else 0 end) +
   (case when p.`desc` like "Import"
        then 50 else 0 end)
) as priority
from item i , product p
where (
    p.`name` LIKE "cleaning agent" 
    and p.`prodid` = i.`itemid`
)

The query does fine in adding a "priority" value to each product but what I would like to ask is how do I group them by ID and total the priority based on the ID? I can group similar prodid rows with the Order by keyword, but then it just gives me a single value for the priority field.

What I want to achieve is to group all similar product id's and get a total of their priority value. I've used sum() in select statements before, but I'm at a loss at trying to figure out how to get the total of all priority fields because it is a query-generated column.

+--------+----------+
| prodid | priority |
+--------+----------+
| 225    | 50       |
| 225    | 20       |
+--------+----------+

should be 

+--------+----------+
| prodid | priority |
+--------+----------+
| 225    | 70       |
+--------+----------+

Here is a sqlfiddle: http://sqlfiddle.com/#!2/cec136/5

Upvotes: 1

Views: 267

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this by turning your query into an aggregation using group by:

select p.`prodid`,
       sum(case when p.`desc` like 'Import/Export' then 100
                when p.`desc` like 'Export' then 70 
                when p.`desc` like 'Import' then 50
                else 20
            end) as priority
from item i join
     product p
     on p.`prodid` = i.`prodid`
where p.`type` LIKE 'cleaning agent'
group by p.prodid;

Along the way, I fixed a few things:

  • The join is now explicit in the from clause, rather than implicit in the where clause.
  • Because i.prodid = p.prodid, there is no need to include both in the select.
  • I changed the case statement to cascade. Only one of the conditions can match, so there is no reason to add things together.
  • I changed all the string constants to use single quotes rather than double quotes.

Upvotes: 1

Related Questions