CODEWITHSUNDEEP
phpmysqlsqldatabasegroup-by
P G
P G

Reputation: 23

How to use group by to get multiple result set? grouping on multiple columns

I have a table named as product which contains following data 

pid            brand     color      

1              sony      white
2              casio     gray
3              xoxo      blue
4              samsung   white
5              mvc       silver
6              sony      gray
7              xoxo      red
8              samsung   silver
9              mvc       white

I need to get total count of products belongs to each brand & color. So i wrote 2 different queries.

Select count(pid) as total from products group by brand;
Select count(pid) as total from products group by color;

This works fine. But since my product table data is huge, I think its better if I can achieve this by a single query. Is it possible?

Upvotes: 2

Views: 2980

Answers (7)

anjali
anjali

Reputation: 1

$sql = "SELECT col_1, SUM(col_2), SUM(col_3) FROM table_name GROUP BY(col_1)";

Upvotes: -1

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

It is possible using with statements, but this is not available in MySQL at the time of writing this answer.

Upvotes: 0

John Gibb
John Gibb

Reputation: 10773

Grouping separately by two different columns results in two fundamentally different queries, so from the database's perspective, it's easier to run them separately. I'd look into creating two indexes (one on brand, one on color) to help.

Upvotes: 0

Parris Varney
Parris Varney

Reputation: 11478

select brand as `group`, count(pid) from products group by brand
union all
select color as `group`, count(pid) from products group by color

Upvotes: 0

jack.mike.info
jack.mike.info

Reputation: 128

select 
count(*) over (partition by product), 
count(*) over (partition by color)
from table

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

Depending on how huge your table actually is, it might be cheaper to get the totals for brand/color combinations first and store them to a temporary table, and then query the temp table twice to find the final totals by each criterion.

That is, something like this:

-- create a temporary table (omitted)

INSERT INTO temp_table (brand, color, total)
SELECT brand, color, COUNT(*) AS total
FROM products
GROUP BY brand, color

SELECT brand, SUM(total) AS total
FROM temp_table
GROUP BY brand

SELECT color, SUM(total) AS total
FROM temp_table
GROUP BY color

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

You could simply have the UNION of these two queries:

( SELECT 1        AS groupType
       , brand    AS grp
       , count(*) AS total
  FROM products 
  GROUP BY brand
)
UNION ALL
( SELECT 2        AS groupType
       , color    AS grp
       , count(*) AS total
  FROM products 
  GROUP BY color
)
ORDER BY groupType
       , grp

If you want the number of products for every brand-color combination, you should have the table grouped by these two fields:

SELECT brand
     , color
     , count(*) AS total
FROM products 
GROUP BY brand
       , color

Upvotes: 2

Related Questions