Mysql query result generate months(1-12) for each category and arrange in same column order by category

Question

I want to generate months no for 1-12 for each category. There are two categories, DEBIT and CREDIT. So, after finish print 1-12 for DEBIT. Then, it should go to next category and print the same with CREDIT in category column.

SELECT mon, @c:=@c+1 as cat_no, category
FROM
(
 SELECT @m:=@m+1 as mon FROM
(   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION 
  SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12)a,
 (SELECT @m:=0)c
)d,

(SELECT 'DEBIT' as category UNION SELECT 'CREDIT' as category)b,
(SELECT @c:=0)e

The fiddle here.

Result:

The result show the column category display two categories for each month before go to next. But, I expected to output all 1-12 before go to next month.

Expected:

Thank you.

version 5.6.36-83.0

Premlatha
– Premlatha

2021-09-22 07:45:10 +00:00
Commented Sep 22, 2021 at 7:45 — Premlatha
– Premlatha, Commented Sep 22, 2021 at 7:45

Akina · Accepted Answer · 2021-09-22 07:56:02Z

2

SELECT mon.mon, cat.cat_no, cat.category
FROM ( SELECT 1 mon UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
       SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION 
       SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 ) AS mon
CROSS JOIN ( SELECT 1 cat_no, 'DEBIT' category UNION
             SELECT 2, 'CREDIT' ) cat
ORDER BY cat_no, mon

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=60416e7875ba7eb886e804c0bddbcadb

edited Sep 22, 2021 at 7:56

answered Sep 22, 2021 at 7:44

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Premlatha Over a year ago

mysql version is 5.6.36-83.0. Cross join not work. It showing error. Is there any alternative way?

Akina Over a year ago

@Premlatha Cross join not work. No, CROSS JOIN works successfully. You have not read the error message carefully. Ver. 5.6 does not allow column names list in the subquery alias. Fixed due to your version, according fiddle added.

Premlatha Over a year ago

yes it works . Sorry for not looking it properly. Thank you for you answer.

Collectives™ on Stack Overflow

Mysql query result generate months(1-12) for each category and arrange in same column order by category

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related