CODEWITHSUNDEEP
sqlsql-servert-sql
Pirate X
Pirate X

Reputation: 3093

Group By with Union causing error

I want to merge output of two queries -

select top(10) hex_code from dbo.colors

Output -

+----------+
| hex_code |
+----------+
| #2ecc71  |
| #3498db  |
| #9b59b6  |
| #f1c40f  |
| #e67e22  |
| #e74c3c  |
| #2980b9  |
| #2c3e50  |
| #27ae60  |
| #f39c12  |
+----------+

Query -

SELECT top(10) [Product], count([Product]) as Count
  FROM [dbo].[TableA] group by [Product] order by count([Product]) desc

Output -

+---------+-------+
| Product | Count |
+---------+-------+
| A       |   105 |
| B       |    99 |
| C       |    87 |
| D       |    75 |
| E       |    56 |
| F       |    52 |
| G       |    37 |
| I       |    18 |
| K       |    16 |
| L       |    15 |
+---------+-------+

I tried using UNION to merge the output but the group by clause is not letting me. I am not sure how to use it with GROUP BY and ORDER BY clause.

I tried -

SELECT top(10) [Product], count([Product]) as Count
  FROM [dbo].[TableA] group by [Product] order by count([Product]) desc
UNION
    select top(10) hex_code from dbo.colors

But this results in error. Any other way to merge these two columns ?

EDIT - Expected Output

+---------+-------+----------+
| Product | Count | Hex Code |
+---------+-------+----------+
| A       |   105 | #2ecc71  |
| B       |    99 | #3498db  |
| C       |    87 | #9b59b6  |
+---------+-------+----------+
for all 10 rows.

With ScaisEdge's answer, the out is coming like

A    105    #27ae60
A    105    #2980b9

Note - Both columns fetch top 10 records. Both tables are not related. (No joins, I suppose)

Upvotes: 0

Views: 188

Answers (2)

Blorgbeard
Blorgbeard

Reputation: 103507

You need to join the two tables/queries. If you don't have a column to join on, and you just want the to match each product to an arbitrary colour, you could join on row_number(), something like:

select p.Product, p.Count, c.hex_code
from (
    SELECT top(10) 
        [Product], count([Product]) as Count,
        row_number() over (order by count([Product])) [rn]
    FROM [dbo].[TableA] 
    group by [Product] 
) p
left join (
    select top(10) 
        hex_code, 
        row_number() over (order by hex_code) [rn]
    from dbo.colors
) on p.rn=c.rn
order by p.Count desc

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28910

If arbitrary,you can do this..

    ;With cte
    as
    (
    SELECT top(10) [Product], count([Product]) as Count
      FROM [dbo].[TableA] group by [Product] order by count desc
    )
  ,cte1
   as(
       select top 10 hex_code from dbo.colors 
     )
    select * from cte c
    cross apply
    (select top 1 hex_code from cte1 order by newid()
    )b

Upvotes: 0

Related Questions